I have 2 tables which store information. One is a main table, and the other is an archive table. They store exactly the same information - i.e. the structure is the same. There is a checkbox in the main table and when users are done with an item they check that and it is moved to the archive table.

I need to generate a query which will treat those two tables as one table.

For example I need reporting by date for how many items received. I can do this for each table sperately but not together, at least not how I want to.

I need the results of the query to have the date in one column and category in another column and count of how many items in another column. But for both tables at once.

Is this possible? Am I being clear enough? TIA!

I would question the use of 2 tables, but:

SELECT Field1, Field2
FROM Table1
UNION ALL
SELECT Field1, Field2
FROM Table2

Use a union query like this

SELECT [datefield], [Category], [Count]
FROM [Maintable]

UNION SELECT [datefield], [Category], [Count]
FROM [archive]
ORDER BY [datefield];

Thanks guys! That should work. Googled the UNION command for SQL and read up on it. Hadn't heard of that option before.

Paul - I would also prefer one table, but I didn't design this guy...just gotta put some reports into an existing database.