Results 1 to 6 of 6
  1. #1
    RunTime91 is offline Competent Performer
    Windows 8 Access 2013
    Join Date
    Dec 2014
    Posts
    281

    How to write an Access query that can count total records by individual within a date range

    Greetings...

    I am trying to create a query that counts the # of One-on-One Reviews completed by Supervisor within a given date range

    Problem is, (as most of you reading this have already figured out) adding the 'Totals' component to the Access Query Grid forces a group by on the date field et.al

    Thus I return the count for each Supervisor for each date in the date range - Can't believe there isn't a way to do this

    I've tried using Sum and about EleventeenHundred other itterations - Result - Here I am...

    Below is the latest effort which produces the aforementioned results (Counts by date instead of total count for date range)
    Code:
    SELECT SubCorrectiveAction.DateActionIssued, MainCorrectiveAction.EnteredBy, SubCorrectiveAction.TypeOfAction, Count(MainCorrectiveAction.EnteredBy) AS Total
    FROM (MainCorrectiveAction INNER JOIN SubCorrectiveAction ON (MainCorrectiveAction.[Corr-ID] = SubCorrectiveAction.[Corr-ID]) 
    AND (MainCorrectiveAction.AssocID = SubCorrectiveAction.AssocID)) INNER JOIN tri_vAssocView ON SubCorrectiveAction.AssocID = tri_vAssocView.Assoc_ID
    GROUP BY SubCorrectiveAction.DateActionIssued, MainCorrectiveAction.EnteredBy, SubCorrectiveAction.TypeOfAction
    HAVING (((SubCorrectiveAction.DateActionIssued)>=#2/1/2020#) AND ((SubCorrectiveAction.TypeOfAction)="One-on-One Review"));
    As always - Thank You so much for any help.



    Rt91

  2. #2
    pbaldy's Avatar
    pbaldy is offline Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    22,518
    In design view, change Group By to Where for the date field.
    Paul (wino moderator)
    MS Access MVP 2007-2019
    www.BaldyWeb.com

  3. #3
    RunTime91 is offline Competent Performer
    Windows 8 Access 2013
    Join Date
    Dec 2014
    Posts
    281
    Okay so I have the following producing the correct results...I used the query above then built another query to 'SUM' the results of the first query...

    As I mentioned, it works (seems to work) But is there a better way to do this; or a way to combine these two queries (the COUNT query and the SUM query into just one query?

    Any guidance is greatly appreciated
    Code:
    SELECT [Qry_One-on-One2].EnteredBy, Sum([Qry_One-on-One2].Total) AS SumOfTotal
    FROM [Qry_One-on-One2]
    GROUP BY [Qry_One-on-One2].EnteredBy;
    Thank you...

  4. #4
    RunTime91 is offline Competent Performer
    Windows 8 Access 2013
    Join Date
    Dec 2014
    Posts
    281
    Oh Geez!

    Paul - Thank You So Much!!!!!!!!!!!!

    That is exactly what I was trying to do...

  5. #5
    pbaldy's Avatar
    pbaldy is offline Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    22,518
    Happy to help! Same thing stumped me at first too.
    Paul (wino moderator)
    MS Access MVP 2007-2019
    www.BaldyWeb.com

  6. #6
    CarlettoFed is offline Competent Performer
    Windows 7 64bit Access 2013 32bit
    Join Date
    Dec 2019
    Posts
    255
    In this example you can achieve the same result with a single query
    Code:
    SELECT MainCorrectiveAction.EnteredBy, Count(MainCorrectiveAction.EnteredBy) AS Total
    FROM MainCorrectiveAction INNER JOIN SubCorrectiveAction ON MainCorrectiveAction.[Corr-ID] = SubCorrectiveAction.[Corr-ID]
    WHERE (((SubCorrectiveAction.DateActionIssued)>=#2/1/2020#) AND ((SubCorrectiveAction.TypeOfAction)="One-on-One Review"))
    GROUP BY MainCorrectiveAction.EnteredBy
    Of course it is very restrictive to use a query by entering the search parameters in it as every time you want to change the search criteria you have to open it and edit them.
    You should use a mask where in the header you enter the controls that should filter the data r in the body a subform where the data is to be filtered.
    Entering or selecting the data in the search controls, those in the subform would be dynamically filtered.

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

Similar Threads

  1. Replies: 3
    Last Post: 10-01-2014, 04:09 PM
  2. Replies: 11
    Last Post: 05-09-2014, 12:00 PM
  3. Replies: 2
    Last Post: 04-15-2014, 01:59 PM
  4. Replies: 1
    Last Post: 01-24-2013, 05:50 PM
  5. Replies: 3
    Last Post: 01-25-2011, 09:50 AM

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