Query Help

[Gyetko]

I've posted an example of the Query results I would like returned from
my Data Source. Basically, I want to have one row for each item, then
have columns for the Months and show the Qty for that Item under the
given Month. I know I can get my desired results using VBA, but was
wondering if it could be done with straight SQL. Thank you.

Data Source:
Item Month Qty
--------- --------- ------
12345 1 50
12345 2 65
12345 3 42
67890 1 15
67890 2 11
67890 3 26

Query Results:
Item QtyMonth1 QtyMonth2 QtyMonth3
--------- ---------------- ---------------- ----------------
12345 50 65 42
67890 15 11 26

 

[John Spencer]

Take a look at crosstab queries. A crosstab query will probably give you
exactly what you have asked for.

If you have troubles using the crosstab wizard, post back and we will try to
write the query for you.

Something like
TRANSFORM First(Qty) as TheQty
SELECT Item
FROM YourTable
GROUP BY Item
PIVOT YourTable.Month


--
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
..