monthly quantity report

[daniellchiu]

I got a monthly table

TABLEJAN

id quantity type
1 1 c
2 1 c
3 2 d
4 2 d

TABLEFEB

id quantity type
1 2 e
2 2 e
3 3 d
4 3 d


how can i make a query to become like this

month SUM(e) SUM(d) SUM(c)
JAN 0 4 2
FEB 4 6 0



thx

 

[Duane Hookom]

I'm not sure why you have separate tables by month. However, you can combine
them with a union query like:
SELECT "JAN" As Mth, ID, Quantity, Type
FROM TABLEJAN
UNION ALL
SELECT "FEB", ID, Quantity, Type
FROM TABLEFEB;

Then create a crosstab based on the union query with Mthas the Row Heading,
Sum(Quantity) as the Value, and Type as the Column Heading.

 

[daniellchiu]

thx alot its works ,But HOW can i "rearrange the order" of month row such
as below from E, D, C...??????

month SUM(e) SUM(d) SUM(c)
JAN 0 4 2
FEB 4 6 0

 

[Dale Fye]

Instead of just using [Type] as the field name for your column headers, use
something like:

MyType:"Sum(" & [Type] & ")"

HTH
Dale

 

[Duane Hookom]

Change the SQL to:
SELECT 1 As MthNum, "JAN" As Mth, ID, Quantity, Type
FROM TABLEJAN
UNION ALL
SELECT 2, "FEB", ID, Quantity, Type
FROM TABLEFEB;

You can use the MthNum column to get the correct order in your report.