Query Question

[sdy007]

Hello,



In my problem I have multiple tables by month. In each table, there are 3 columns: ID, date, and number of orders.
In the SQL code, I have tried to create a query that UNION the ID numbers from all months by doing "SELECT [col1] FROM [Dec] UNION SELECT [col1] FROM [Jan]...etc.

The problem I have is that I don't know how to have additional columns in the query after doing that. Can anyone help me out?

Ultimately I want a table/query that has the distinct ID #s in one column and then distinct dates from all months going across the fields, and each cell would contain the number of orders for that ID on that date. So I guess another problem is that there are some IDs not ordering on all dates so how do I handle that?

Thanks

[Dal Jeanis]

Need to know the exact form of the tables to give you the exact method. Not sure if ID is a customer ID, a transaction ID, or what. Sounds like you've designed yourself into a box, by creating a new table each month when you don't have a real need for it.

That being said, first you UNION the tables together to get a massive table. We'll call that query "query1" -

SELECT ID, ordDate, ordCount FROM [Jan]
UNION
SELECT ID, ordDate, ordCount FROM [Feb]
UNION
SELECT ID, ordDate, ordCount FROM [Mar]
UNION
SELECT ID, ordDate, ordCount FROM [Apr]
(etc)

Second, you're going to take that query and PIVOT on the date -

TRANSFORM   SUM(ordcount) As OrderCount
SELECT      ID,
            ordCount
FROM        query1  
GROUP BY    ID
PIVOT       ordDate

I'm not sure whether PIVOT will choke on the UNION or not - PIVOTs are notoriously finnicky. If it does, then just use your query to create a temporary table and use the table in the PIVOT.

Any cells where the ID had no ordCount for that date will be empty.