LostReality LostReality - 3 months ago 14
SQL Question

Sum fields from mutiple tables with conditions

I'm working on a quite big project and I need to compute some values through SQL. I have 3 datasets with quite identical fields :

Query1 : txt groupName, int cat, long hoursSubTot1
Query2 : txt groupName, int cat, long hoursSubTot2
Query3 : txt groupName, int cat, long hoursSubTot3


I need to add hoursSubTot1,hoursSubTot2 and hoursSubTot3 like that :
enter image description here

I would like the datas to be agregate as shown in the result table. But I do not manage to agregate the right values in Access, I am out of ideas so any help or advice would be welcome...

Thanks!

Answer

If you're not opposed to using multiple queries, here's my thought. First create a query using UNION ALL to combine the three queries. Then you can create a new query to calculate the sums.

SELECT Group, Category, Hours FROM Table1
UNION ALL
SELECT Group, Category, Hours FROM Table2
UNION ALL
SELECT Group, Category, Hours FROM Table3