locked
Query Question RRS feed

  • Question

  • I have an access 2013 DB that has a table with a bunch of training records in it. Example of some of the data appears below. I don’t think I need to explain how I retrieved this data. (There are thousands of records.)

     

    3-QueryToListAnnualTrainingDocumentsActiveUsersHaveRecordsFor

    CLOCK_NUMBER

    ClockLength

    TRAINING_DOCUMENT_TAKEN

    DocLength

    KEY

    DateCompleted

    Year

    308

    3

    02-04A

    6

    02-04A-308

    11/3/2014

    0

    308

    3

    02-04A

    6

    02-04A-308-2

    10/20/2015

    2

    308

    3

    02-04A

    6

    02-04A-308-3

    10/4/2016

    3

    308

    3

    02-04A

    6

    02-04A-308-4

    9/19/2017

    4

     

     These records represent the date a user completed an annual training requirement.  The key field is a unique value that is built when the user completes the training requirement.  In the example above, the first time user number 308 completed the requirement, the key was 02-04A-308.  The next year it was 02-04A-308-2, and so on.

     

    I want to create a query that retrieves the record with the largest year value for all users.  It would appear that I cannot use a Totals Query with the max parameter, since it would need to group on the key field, and return all the values even if I used Max on the year field.

     

    My ultimate goal is to “Assign” the requirement annually when the date completed is over a year ago.  The key will be incremented.  i.e. 02-04A-308-5 for this example.

     

    Any suggestions?

    Monday, April 16, 2018 3:32 PM

Answers

  • I think you need to build a totals query where you groupby your ID (clock_number) and max for the datecompleted, then you use that in a new query joining both on clock_number and maxofdatecompleted.

    Cheers,

    Vlad

    • Marked as answer by tkosel Monday, April 16, 2018 6:43 PM
    Monday, April 16, 2018 3:44 PM

All replies

  • I think you need to build a totals query where you groupby your ID (clock_number) and max for the datecompleted, then you use that in a new query joining both on clock_number and maxofdatecompleted.

    Cheers,

    Vlad

    • Marked as answer by tkosel Monday, April 16, 2018 6:43 PM
    Monday, April 16, 2018 3:44 PM
  • Yes, it turns out that if I use the Max value in a totals query for both Key and date completed, I get the correct records.  Thanks for pointing me in the correct direction.

    I was wrong, what I said does not work!!!  Ignore what I said. 

    Vlad, I followed your advice and it works fine!  Thanks.


    • Edited by tkosel Monday, April 16, 2018 6:43 PM correction
    Monday, April 16, 2018 3:54 PM