Results 1 to 2 of 2
Thread: Group By Problem (2000)
2004-08-24, 17:11 #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.
2004-08-24, 17:27 #2
- Join Date
- Mar 2002
- Thanked 28 Times in 28 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.