Unanswered: add values of fields from two different tables
Hello, I've been trying to resolve this problem without any good result, I would really thank your help.
Having table A and table B with fields "Project" and "Euros" in each table,
I'm trying to make a query that will show as a result tree four fields:
Project - will show the name of the Project.
Total Euros A - will add all the values of the field Euros in Table A.
Total Euros B - will add all the values of the field Euros in Table B.
Total Project - will add Total Euros A + Total Euros B
* Consider that:
- most of the Projects will have one or various values in both tables A and B
- but that others Projects may have many values in one of the tables and none in the other table.
You can use the SQL SUM function in a query:
SELECT SUM (<Field1>) AS Total1
SELECT SUM (<Field1>) AS Total2
You can create several queries then combine them together in an INSERT INTO query:
INSERT INTO <Table3> (<FieldA>, <FieldB>)
SELECT Total1, Total2
FROM Query1, Query2
It's also possible to process this kind of operation in a single query that combines the SELECT SUM()'s and the INSERT INTO, but Access does not always accept that ("Expression too complex", or something like that).