Query to combine 2 results

[ripon via AccessMonster.com]

Hi All:
I have a table with integer data as follows:
book pen bag month cyear
10 20 30 1 2004
15 20 5 2 2005
5 5 5 2 2004
20 15 5 1 2005
--Now can I make a query to get results like:
-- feb04 feb05 jan-feb04 jan-feb05
book 5 15 15 35
pen 5 20 25 35
bag 5 5 35 10

--Thanks in advance

 

[Duane Hookom]

The first step is to normalize your "I have a table". If you can't do this,
use a union query to create a normalized recordset:
SELECT Book as Qty, "Book" As Item, DateSerial(Cyear, Month, 1) as TheDate
FROM [I have a table]
UNION
SELECT Pen, "Pen", DateSerial(Cyear, Month, 1)
FROM [I have a table]
UNION
SELECT Bag, "Bag", DateSerial(Cyear, Month, 1)
FROM [I have a table];

You can then create a crosstab query with Item as the Row Heading,
Format(TheDate,"mmmyy") as the Column Heading, Sum(Qty) as the Value.

To get the jan-feb totals you can add row headings like:
JanFeb2004: Abs(Year(TheDate=2004)) * Qty
Sum