LostReality LostReality - 1 year ago 92
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 Source

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
Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download