Apologies in advance if I've made a simple yet glaring error with the following, however I am a total novice when it comes to Access and I'm very much still finding my feet.
I have 6 separate queries, all pulling data from the same table, with each giving me a single numerical result. Each individual query populates a box on my form. I have a 7th box, in which I would like to place the total of the 6 results.
So far I've gotten so far as making a union query, however this has only managed to place all 6 results into a single list in datasheet view.....
Is it possible to sum the results of the 6 queries to give one single total?
My union query in SQL is as follows...
SELECT Count(tblConcern.ConcernID) AS CountOfConcernID, tblConcern.[Raised Dept]
FROM tblConcern
GROUP BY tblConcern.[Raised Dept]
HAVING (((tblConcern.[Raised Dept])="ERF"))
UNION ALL
SELECT Count(tblConcern.ConcernID) AS CountOfConcernID, tblConcern.[Raised Dept]
FROM tblConcern
GROUP BY tblConcern.[Raised Dept]
HAVING (((tblConcern.[Raised Dept])="ERP"))
UNION ALL
SELECT Count(tblConcern.ConcernID) AS CountOfConcernID, tblConcern.[Raised Dept]
FROM tblConcern
GROUP BY tblConcern.[Raised Dept]
HAVING (((tblConcern.[Raised Dept])="Maintenance"))
UNION ALL
SELECT Count(tblConcern.ConcernID) AS CountOfConcernID, tblConcern.[Raised Dept]
FROM tblConcern
GROUP BY tblConcern.[Raised Dept]
HAVING (((tblConcern.[Raised Dept])="Production"))
UNION ALL
SELECT Count(tblConcern.ConcernID) AS CountOfConcernID, tblConcern.[Raised Dept]
FROM tblConcern
GROUP BY tblConcern.[Raised Dept]
HAVING (((tblConcern.[Raised Dept])="Safety"))
UNION ALL S
ELECT Count(tblConcern.ConcernID) AS CountOfConcernID, tblConcern.[Raised Dept]
FROM tblConcern
GROUP BY tblConcern.[Raised Dept]
HAVING (((tblConcern.[Raised Dept])="Supplier"));
I have a feeling there's a much easier way of doing this, as I want the results in text boxes, and I'm currently cheating by using a list box (so I can use the row source) but I haven't quite figured out how to use DCount required to get the query results into a text box.
Any help greatly appreciated.