cross tab query

[Guest]

I have 2 tables, One has onhand qty info and the other
usage for the year

Table1 Table2

Part1 100 Part1 Jan 2
Part2 200 part1 feb 3
Part3 5 part2 feb 1

I want the results to look like...

Onhand Jan Feb
part1 100 2 3
part2 200 1
part3 5


Any help?

Thanks

 

[Ron Weiner]

I think you are going to have to do this with two queries. The first is a
Crosstab query that gets used by the second query to output the desired
result.

Here is the Crosstab query

TRANSFORM Sum([Table2].[QtySold]) AS SumOfQtySold
SELECT [Table2].[Part]
FROM Table2
GROUP BY [Table2].[Part]
PIVOT [Table2].[Month]
IN ('Jan','Feb','Mar','Apr','May','Jun','Jul','Aug','Sep','Oct','Nov','Dec')

Save it in your database as "Table2_Crosstab"

Here is the Second query that joins Table1 with the above Crosstab query
providing the output you desire

SELECT Table1.Part, QtyOnHand, Jan, Feb, Mar, Apr, May, Jun, Jul, Aug, Sep,
Oct, Nov, Dec
FROM Table1 LEFT JOIN Table2_Crosstab ON Table1.Part = Table2_Crosstab.Part

Ron W