Calculate between linked tables

  • Thread starter Thread starter Jeroen
  • Start date Start date
J

Jeroen

I have a database with e.g. 15 linked tables from excel. So all 15 tbales
have the same layout. The 1st column has months in it.

How can i calculate the values for columns B C etc between all tables for
the same month value

Table 1 Table 2
A B C D A B C D
Jun09 5 Jun09 6
Jul09 2 Jul09 5

Rapport
A B C D
Jun09 11
Jul09 7
 
Create a Union All query that joins all the tables together.

SELECT a, b, c, d
FROM [Table 1]
UNION ALL
SELECT a, b, c, d
FROM [Table 2]
UNION ALL
SELECT a, b, c, d
FROM [Table 3]
UNION ALL
SELECT a, b, c, d
FROM [Table 4]

and so on for all 15 tables with the proper table and field names.

Save that query. Next create a Totals query based on the one above using the
first field as the Group By and Sum the other fields.
 
Back
Top