1. This site uses cookies. By continuing to use this site, you are agreeing to our use of cookies. Learn More.

Combining Multiple Query Results

Discussion in 'MS Access' started by gazzjones, Dec 4, 2006.

  1. gazzjones

    gazzjones New Member

    Joined:
    Dec 4, 2006
    Messages:
    1
    Likes Received:
    0
    Trophy Points:
    0
    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...
     

Share This Page