MS access query using many tables

Question

I have a database with 12 tables with pension details of employees for Jan-Dec 2008. I want to get the total pension for 12 months for each employee adding up amounts from all the 12 tables (jan through dec).

How should I write the query?

Answer 1

Why on earth do you have 12 different tables? That's a fundamentally bad design.

Anyway...

Select
  EmployeeID, Total2008 = Sum(Value)
From
  (Select EmployeeID, Value From JanuaryTable
  Union All
  Select EmployeeID, Value From FebruaryTable
  ...
  Select EmployeeID, Value From DecemberTable)
Group By
  EmployeeID

Answer 2

You may want to reconsider this design, and instead have a single table with a column to indicate MONTH and YEAR.

Given your current design, your query will need to look like this (substituting your own table and field names, obviously):

First, create this query and save it as "Monthly Totals":

SELECT EmployeeId AS EmployeeNumber, SUM(Pension_Amount) AS Pension_Totals
FROM Pension_January
GROUP BY EmployeeId
UNION ALL
SELECT EmployeeId, SUM(Pension_Amount)
FROM Pension_February
GROUP BY EmployeeId
UNION ALL
.....Other months.....
UNION ALL
SELECT EmployeeId, SUM(Pension_Amount)
FROM Pension_December
GROUP BY EmployeeId;

Second, create another query that refers to the first:

SELECT
  [Monthly Totals].EmployeeNumber,
  SUM([Monthly Totals].Pension_Totals) AS Employee_Total
FROM [Monthly Totals]
GROUP BY [Monthly Totals].EmployeeNumber;

Now save the second query and run it. It should give you what you need.