Query several tables and combine results together

[mralmackay]

I want to be able to query all tables within an access database (I
would prefer if possible not to have to hard code in each table) to
query a field called 'Received' and count by month/year how many items
exist.

For example 3 of the tables are:
ebsAmendments
ebsBatch Scheduling
ebsChangeManagement

In this example I would want the data to show as:
Jan 06 Feb 06 Mar 06
ebsAmendments 22 35 53
ebsBatch Scheduling 25 38
ebsChangeManagement 23 21

Appreciate any help you may be able to offer, can do by individual
table but wanted one report that would show it all combined?

Cheers, Al.

 

[John Spencer]

The only way I can see to do this would be to use a union query as the basis
for a crosstab query. This would mean you have to hardcode each table into
the query.

SELECT Received, "ebsAmendments" as Source
FROM ebsAmendments
UNION ALL
SELECT Received, "ebsBatchScheduling" as Source
FROM ebsBatchScheduling
UNION ALL
SELECT Received, "ebsChangeManagement" as Source
FROM ebsChangeManagement

With that saved query, then you could build a crosstab query to get the data
you want in the format you want.