Results 1 to 2 of 2
  1. #1
    Join Date
    Jan 2002
    Thanked 0 Times in 0 Posts

    Group By Problem (2000)

    I'm trying to create a query that groups contracts by contract numbers. Each contract number may have more than one contract type though. I'm trying to build a query that lists each contract number and the total value of all contract types (I got that part) and then list all of the contract types in a single row (or ideally in a single field). So I would like it to read:

    # Value Types
    1 $100 A, B

    Any suggestions? Thanks.

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Thanked 31 Times in 31 Posts

    Re: Group By Problem (2000)

    You need a custom function to concatenate the contract types. You can find the code for such a function attached to <post#=301393>post 301393</post#>. Copy the code into a standard module (created by clicking New in the Modules section of the database window). Then, open your query in design view and add a calculated column:

    ContractTypes: Concat("tblSomething", "ContractType", "[ContractNumber] = " & [ContractNumber])

    tblSomething is the name of the table holding the data (it may also be the name of a query).
    ContractType is the name of the contract type field.
    ContractNumber is the name of the contract number field.

    Set the Total option for this column to Expression.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts