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
LVL 3
bamaseaAsked:
Who is Participating?
 
Jeffrey CoachmanMIS LiasonCommented:
bamasea,

From what I can tell, you can also do this with a "Crosstab query"

Here is a sample.

The query is called:
  Sheet1_Crosstab

JeffCoachman
Access-EEQ-24159865CrossTabQuery.mdb
0
 
Rey Obrero (Capricorn1)Commented:
how does the record from the TABLE look like?
0
 
bamaseaAuthor Commented:
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
0
Cloud Class® Course: Ruby Fundamentals

This course will introduce you to Ruby, as well as teach you about classes, methods, variables, data structures, loops, enumerable methods, and finishing touches.

 
Rey Obrero (Capricorn1)Commented:
i'll presume that the number of records varies per size, color, style, is this correct?
0
 
bamaseaAuthor Commented:
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.
0
 
Rey Obrero (Capricorn1)Commented:
you will need vba codes to do this, is this an option?
0
 
bamaseaAuthor Commented:
Yes, of course, assuming I dont need anything special from the vba developer product.
0
 
Rey Obrero (Capricorn1)Commented:
upload a db with only that concerned table.
0
 
bamaseaAuthor Commented:
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;

Open in new window

0
 
Jeffrey CoachmanMIS LiasonCommented:
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  
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.