Combining Multiple Query Results

[gazzjones]

Hi,

I'm putting together a (hopefully) highly automated database that will allow me to produce clear reports for various areas of responsibility in a new job.

I have 3 excel spreadsheets linked directly to access, containing work queue reports. I then run queries that copy new work items into a main table, followed by a query that looks for work items that no longer appear (i.e. work is finished and closed) and then automatically inputs the date.

For reporting purposes i initially want a basic output, i.e. work items raised, and work items closed, for each area of resposnibility. The only trouble i've found is that i have to do a different query for each output, then another query that pulls all the other queries together in order to maniuplate into a graph...

So at the moment i have two queries per responsibility, and one pulling those two together i.e.:

New Work Items:
SELECT Count(ticketsds.id) AS [DS New]
FROM TicketsDS
WHERE (((Format(ticketsds.[date received],"mmm"))=Forms!ReportingDate!comboMonth));
Closed Work Items:
SELECT Count(ticketsds.id) AS [DS Closed]
FROM TicketsDS
WHERE (((Format(ticketsds.[datefinished],"mmm"))=Forms!ReportingDate!comboMonth));

Then i have this query pulling the two results together:
SELECT DSClosed.[DS Closed], DSNew.[DS New]
FROM DSClosed, DSNew;

Is there anyway of combining the first two queries into one, rather than have 3??

Any help would be greatly appreciated, as if i could find out how to do this it woudl enable me to streamline other queries....

Many Thanks!
Gareth

PS I've tried 'UNION' but that just tags the other results under the same field name which i can't use to create graphs...

 

[sxschech]

Is this what you are after?
[tt]
SELECT DISTINCT (SELECT Count(ticketsds.id)
FROM TicketsDS
WHERE Format(ticketsds.[date received],"mmm"))=Forms!ReportingDate!comboMonth) AS NewWorkItems, (SELECT Count(ticketsds.id)
FROM TicketsDS
WHERE Format(ticketsds.[datefinished],"mmm"))=Forms!ReportingDate!comboMonth) AS ClosedWorkItems
FROM TicketsDS;
[/tt]

Or try DSum (example from NorthWind, just change field items to your field names.
[tt]
SELECT DCount("OrderDate","Orders","OrderDate Between #8/1/94# and #8/31/94#") AS Expr1, DCount("RequiredDate","Orders","RequiredDate Between #8/1/94# and #8/31/94#") AS Expr2
FROM Orders;
[/tt]

 

[gazzjones]

thanks sxschech! works like a dream, i was trying to figure out how to nest the SQL statements but couldnt manage it...

only one slight change was the deletion of a few unrequired brackets, working code below:

SELECT DISTINCT (SELECT Count(ticketsds.id)
FROM TicketsDS
WHERE Format(ticketsds.[date received],"mmm")=Forms!ReportingDate!comboMonth) AS NewWorkItems, (SELECT Count(ticketsds.id)
FROM TicketsDS
WHERE Format(ticketsds.[datefinished],"mmm")=Forms!ReportingDate!comboMonth) AS ClosedWorkItems
FROM TicketsDS;


Once again thanks very much for this, helped me out a great deal!