Access request to MS using multiple tables

Question

I have a database with 12 tables with retirement details of employees for January-December 2008. I want to receive a total pension for 12 months for each employee, summing up the sums from all 12 tables (jan through dec).

How to write a request?

Answer 1

You might want to review this project and instead have one table with a column representing MONTH and YEAR.

Given your current design, your query should look like this (obviously replacing your own table and field names):

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;

-, , :

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

. , .

Answer 2

12 ? .

...

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