Results 1 to 9 of 9
  1. #1
    Diggerg is offline Novice
    Windows 7 64bit Access 2013 64bit
    Join Date
    Jul 2018
    Posts
    6

    Adding together the results of multiple queries

    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.

  2. #2
    ranman256's Avatar
    ranman256 is online now VIP
    Windows Vista Access 2010 32bit
    Join Date
    Apr 2014
    Location
    Kentucky
    Posts
    9,521
    why wouldnt you run 1 select query, with criteria: where [raised debt]=ERF or MAINT or Prod or SAFETY or SUPPLIER

    or if the list can change,
    put all [raised debt] items in a table, join the list to the main data table on [raised debt] and get only whats in the list

    THEN run a query to sum the totals from the query above.

    (union queries are better for joining data from different tables together as 1 table)

  3. #3
    Diggerg is offline Novice
    Windows 7 64bit Access 2013 64bit
    Join Date
    Jul 2018
    Posts
    6
    Quote Originally Posted by ranman256 View Post
    why wouldnt you run 1 select query, with criteria: where [raised debt]=ERF or MAINT or Prod or SAFETY or SUPPLIER

    or if the list can change,
    put all [raised debt] items in a table, join the list to the main data table on [raised debt] and get only whats in the list

    THEN run a query to sum the totals from the query above.

    (union queries are better for joining data from different tables together as 1 table)
    Ultimately this comes down to me being an utter novice and putting everything into one table seemed like a great idea when creating this database, as it started out relatively basic, however now it seems to have evolved into something entirely different altogether and with each additional request comes a whole new level of skill that I do not posess, the thought of creating more tables and having to change the whole thing is what I am ultimately trying to avoid.

    The [RAISED DEPT] is assigned via a combo box on the form, so it changes each time a record is created.

  4. #4
    Join Date
    Apr 2017
    Posts
    1,673
    Code:
    SELECT Count(tblConcern.ConcernID) AS CountOfConcernID, tblConcern.[Raised Dept]
    FROM tblConcern
    WHERE tblConcern.[Raised Dept] IN ("ERF","ERP","Maintenance","Production","Safety", "Supplier")
    GROUP BY tblConcern.[Raised Dept]

  5. #5
    Diggerg is offline Novice
    Windows 7 64bit Access 2013 64bit
    Join Date
    Jul 2018
    Posts
    6
    Quote Originally Posted by ArviLaanemets View Post
    Code:
    SELECT Count(tblConcern.ConcernID) AS CountOfConcernID, tblConcern.[Raised Dept]
    FROM tblConcern
    WHERE tblConcern.[Raised Dept] IN ("ERF","ERP","Maintenance","Production","Safety", "Supplier")
    GROUP BY tblConcern.[Raised Dept]
    This is still returning each department as an individual result.

    CountOfConcernID Raised Dept
    3 ERF
    3 ERP
    3 Maintenance
    6 Production
    3 Safety
    3 Supplier

    What steps am I missing to get the box to display the sum of the above numbers ie... 21

  6. #6
    Diggerg is offline Novice
    Windows 7 64bit Access 2013 64bit
    Join Date
    Jul 2018
    Posts
    6
    Also, thank you all for your help, it is much appreciated.

  7. #7
    Join Date
    Apr 2017
    Posts
    1,673
    So you need a total of listed Dept's!
    Code:
    SELECT Count(tblConcern.ConcernID) AS CountOfConcernID
    FROM tblConcern
    WHERE tblConcern.[Raised Dept] IN ("ERF","ERP","Maintenance","Production","Safety", "Supplier")

  8. #8
    Diggerg is offline Novice
    Windows 7 64bit Access 2013 64bit
    Join Date
    Jul 2018
    Posts
    6
    Thank you, I worked it out soon after your original post.

  9. #9
    Gicu's Avatar
    Gicu is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Jul 2015
    Location
    Kelowna, BC, Canada
    Posts
    4,101
    or you could have added the 6 bound textboxes (in the ControlSource property of the 7th) right on the form.

    Cheers,
    Vlad

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Replies: 3
    Last Post: 07-01-2016, 08:11 AM
  2. Creating a report with results of multiple queries
    By megatronixs in forum Queries
    Replies: 2
    Last Post: 05-31-2016, 01:24 AM
  3. ADDING Not showing results
    By keiath in forum Forms
    Replies: 5
    Last Post: 02-12-2014, 02:33 PM
  4. Replies: 3
    Last Post: 09-04-2013, 03:21 PM
  5. Replies: 5
    Last Post: 05-21-2013, 02:21 PM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums