none
Acess Query Count Help RRS feed

  • Question

  • Hi,

    I track all computer issues at work. I have two tables. One table is for tracking issues[Tracker]. The second is a list of employees and what department they belong to[Master ID List]. I have the relationship link between the two tables by employee name. I am trying to count all the issues by department within a selected month. Can someone assist on what SQL view should look like for this?

    Thanks!

    Tuesday, February 7, 2017 3:32 PM

Answers

  • I broke this down into two queries to make it easier (one is nested). The inner query performs the join and filters by the date. The outer query performs the grouping/count aggregate. Let me know if this is what you are looking for.

    SELECT Function, Count(Function) As [Issue Count] FROM (SELECT [Master ID List].Function, [Master ID List].APS
    FROM [Master ID List] INNER JOIN Tracker ON [Master ID List].APS = Tracker.[Agent Name]
    WHERE (((Month([Date/Time Received]))=Month([Insert Month & Year - mm/yyyy])) AND ((Year([Date/Time Received]))=Year([Insert Month & Year - mm/yyyy]))))
    GROUP BY Function;
    
    Sometimes trying to do everything in a single query can be a bit of a head scratcher.

    Paul ~~~~ Microsoft MVP (Visual Basic)

    • Marked as answer by Jesse Torres Wednesday, February 8, 2017 9:17 PM
    Wednesday, February 8, 2017 12:53 AM

All replies

  • How about using a GROUP BY with a COUNT:

    http://www.gcflearnfree.org/access2007/using-queries-to-make-data-meaningful-part-2/3/


    Paul ~~~~ Microsoft MVP (Visual Basic)

    Tuesday, February 7, 2017 3:51 PM
  • So I got it to count by group but am now having issues with having it count within a selected date range by month. I would like an input box to pop up so the user can input a month and year at the beginning of running the query. This is what my SQL looks like on the query so far.

    SELECT [Master ID List].Function, Count(Tracker.[Agent Name]) AS [CountOfAgent Name], Tracker.System
    FROM Tracker INNER JOIN [Master ID List] ON Tracker.[Agent Name] = [Master ID List].APS
    GROUP BY [Master ID List].Function, Tracker.System
    ORDER BY [Master ID List].Function;


    Tuesday, February 7, 2017 4:11 PM
  • So I got it to count by group but am now having issues with having it count within a selected date range by month. I would like an input box to pop up so the user can input a month and year at the beginning of running the query. This is what my SQL looks like on the query so far.

    SELECT [Master ID List].Function, Count(Tracker.[Agent Name]) AS [CountOfAgent Name], Tracker.System
    FROM Tracker INNER JOIN [Master ID List] ON Tracker.[Agent Name] = [Master ID List].APS
    GROUP BY [Master ID List].Function, Tracker.System
    ORDER BY [Master ID List].Function;


    Sounds like you just need to add the WHERE clause:

    https://support.office.com/en-us/article/Examples-of-query-criteria-3197228c-8684-4552-ac03-aba746fb29d8#bm4


    Paul ~~~~ Microsoft MVP (Visual Basic)

    Tuesday, February 7, 2017 4:58 PM
  • So this is what I have so far but I keep getting the same results regardless of what date is inputted.

    SQL:

    SELECT Format([Insert Month & Year - mm/yyyy],"mm/yyyy") AS [Month of Issues],
    [Master ID List].Function, Count(Tracker.[Agent Name]) AS [CountOfAgent Name], Tracker.System
    FROM Tracker INNER JOIN [Master ID List] ON Tracker.[Agent Name] = [Master ID List].APS
    GROUP BY [Master ID List].Function, Tracker.System
    ORDER BY [Master ID List].Function;
    I set criteria as:
    Year(Tracker.[Date/Time Received]) = Year([Month of Issues]()) And Month(Tracker.[Date/Time Received]) = Month([Month of Issues]())


    Tuesday, February 7, 2017 6:01 PM
  • I broke this down into two queries to make it easier (one is nested). The inner query performs the join and filters by the date. The outer query performs the grouping/count aggregate. Let me know if this is what you are looking for.

    SELECT Function, Count(Function) As [Issue Count] FROM (SELECT [Master ID List].Function, [Master ID List].APS
    FROM [Master ID List] INNER JOIN Tracker ON [Master ID List].APS = Tracker.[Agent Name]
    WHERE (((Month([Date/Time Received]))=Month([Insert Month & Year - mm/yyyy])) AND ((Year([Date/Time Received]))=Year([Insert Month & Year - mm/yyyy]))))
    GROUP BY Function;
    
    Sometimes trying to do everything in a single query can be a bit of a head scratcher.

    Paul ~~~~ Microsoft MVP (Visual Basic)

    • Marked as answer by Jesse Torres Wednesday, February 8, 2017 9:17 PM
    Wednesday, February 8, 2017 12:53 AM
  • Thanks Paul!! This looks like its working for me great!
    Wednesday, February 8, 2017 9:17 PM