• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 1086
  • Last Modified:

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
0
bamasea
Asked:
bamasea
  • 4
  • 4
  • 2
1 Solution
 
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
 
Rey Obrero (Capricorn1)Commented:
i'll presume that the number of records varies per size, color, style, is this correct?
0
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
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
 
Jeffrey CoachmanCommented:
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
 
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 CoachmanCommented:
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

Featured Post

Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

  • 4
  • 4
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now