bamasea
asked on
How to combine rows in a group-by query
For Access 2003 mdb, how can I make a query result in grouped data on 3 fields, but return the quantity pertaining to owners all show in the same row. For example:
Current output
size color style Quantity owner
S WH A 5,000 owner1
S WH A 10,000 owner2
Desired output
size color style owner1_qty owner2_qty
S WH A 5,000 10,000
Current output
size color style Quantity owner
S WH A 5,000 owner1
S WH A 10,000 owner2
Desired output
size color style owner1_qty owner2_qty
S WH A 5,000 10,000
Rey Obrero (Capricorn1)
how does the record from the TABLE look like?
ASKER
Quantity is a single column, fields contain owner information. There is a second criteria of type that I would want to seperate also, but the concept is the same, I suppose.
size color style Quantity owner
S WH A 5,000 owner1
size color style Quantity owner
S WH A 5,000 owner1
i'll presume that the number of records varies per size, color, style, is this correct?
ASKER
yes, there are many combinations of size, color, owner, and style, and each of those are individual fields. There are many duplicate combinations, resulting in the need for a summary of quantity report.
you will need vba codes to do this, is this an option?
ASKER
Yes, of course, assuming I dont need anything special from the vba developer product.
upload a db with only that concerned table.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Brilliant! I have been wondering why there wasn't something built into Access that would handle this data. It shows that I don't know the product as well as I should! I am including a copy of your SQL for completeness of this post. Also thanks to capricorn for wheedling all of the pertinent data out of me.
TRANSFORM Sum(Sheet1.Quantity) AS SumOfQuantity
SELECT Sheet1.size, Sheet1.color, Sheet1.style
FROM Sheet1
GROUP BY Sheet1.size, Sheet1.color, Sheet1.style
PIVOT Sheet1.owner;
As Capricorn1 stated, there are many ways to do this.
(Query, VBA, ... you can even do this in a report)
The query works until you need some really crazy, or specific.
Then VBA is required.
JeffCoachman
(Query, VBA, ... you can even do this in a report)
The query works until you need some really crazy, or specific.
Then VBA is required.
JeffCoachman