Results 1 to 11 of 11
  1. #1
    Join Date
    Jul 2009
    Posts
    39

    Unanswered: Query to establish most recent, by date, occurrence of a type of training per person

    Hello hope someone can help with the following.
    Have a staff training database which records amongst other things the names and dates of courses that people have been on. There are 2 courses that I want to query, Epilepsy and Epilepsy Refresher. When someone has done the Epilepsy course they need to do the Epilepsy Refresher course after 12 months. If they fail to do the Refresher course within say 2 years they need to start again and take the initial Epilepsy course I want to know what is the most recent course of either those two that people have done.
    Then having established that say Person A has done the courses several times but the most recent course was say Epilepsy I need to only output the most recent course and date then apply a date comparison with the date of that course and 'today' to see when that course needs to be retaken. Output the date that the course needs to be refreshed and if its overdue by a given amount ( say 12 months ) highlight that record.
    So have related tables for people and courses. I can filter all the training records using the Like (Epilepsy*) expression that gives me all the people who have done any kind of Epilepsy course and lists all such course done per person. First off how do I limit that to just the most recent course per person.
    Then having got that single Person- Course Name- Date need to apply an expression that looks at the single Course Name and applies the appropriate rule to the date comparing it to 'Now' and outputting when teh course needs to be refreshed.
    eg one record shows
    Name Course Name Date of Course
    Fred Smith - Epilepsy - 12/08/2009 I look at the course name 'Epilepsy' , I know that a Refresher Course should follow after say 12 months , I look at the date taken ' 12/08/09' and therefore add 12 months and output into 2 new fields
    Course Name Date To Be Done
    Epilepsy Refresher 12/08/2010

    Hope that makes sense ? Am a novice/moderate user so would appreciate any help offered to treat me as such. Have honestly tried to work it out, Google , search this forum etc but can't find a near enough match.

    Same database and what should be a simpler query but again can't find the answer I need to output a list of people who haven't done any Epilepsy training, Using a query with Not Like " Epilepsy* gets me such a list but it is all the training that people have done which isn't Like " Epilepsy* so several records and therefore instances of people's names. I just need 1 record
    Name - Workbase per person for people have not done any epilepsy training?
    Many thanks for any help
    Keith

  2. #2
    Join Date
    Jul 2009
    Posts
    39

    Max function

    In respect of second part of my post on how to get only the record with the most recent date I have tried the 'max' function. This still gives all the records. Works if I reduce the fields in the query to just the persons name and the Date Completed field. I get the correct, most recent date but don't then have the course name.
    Last edited by kedunc; 10-26-09 at 18:22.

  3. #3
    Join Date
    Nov 2007
    Location
    Adelaide, South Australia
    Posts
    4,049
    Works if I reduce the fields in the query to just the persons name and the Date Completed field. I get the correct, most recent date but don't then have the course name.
    Perhaps this is a table design fault? If you link the courses table to the CourseID which should be in the same table as the date attended, you should get the course name without affecting anything else.

    It would be so much easier if I could see your table structure and your query design.
    Owner and Manager of
    CypherBYTE, Microsoft Access Development Specialists.
    Microsoft Access MCP.
    And all around nice guy!


    "Heck it's something understood by accountants ... so it can't be 'that' difficult..." -- Healdem
    "...teach a man to code and he'll be frustrated for life! " -- georgev

  4. #4
    Join Date
    May 2005
    Location
    Nevada, USA
    Posts
    2,888
    Provided Answers: 6
    Quote Originally Posted by kedunc
    Works if I reduce the fields in the query to just the persons name and the Date Completed field. I get the correct, most recent date but don't then have the course name.
    Does this technique work?

    Most recent value
    Paul

  5. #5
    Join Date
    Jul 2009
    Posts
    39
    Thanks will give those thoughts a go if it doesn't work I'll strip out the private staff data from the DB and upload it.
    Cheers
    Keith

  6. #6
    Join Date
    Jul 2009
    Posts
    39
    Paul thanks , your solution worked , just needed a prior step to filter for the 'epilepsy' courses to make a new table to run the queries on.
    Now need to work out the if statements to produce a Due/Overdue/NotDue return from the Date Completed field depending on which of the two types of courses was most recently completed. Will have a go.
    Cheers

  7. #7
    Join Date
    May 2005
    Location
    Nevada, USA
    Posts
    2,888
    Provided Answers: 6
    No problem, glad it helped. I would have thought a criteria could have been used instead of a new table, but don't know that much about your situation. Post back if you get stuck on the if statements (check out the Switch function as well).
    Paul

  8. #8
    Join Date
    Jul 2009
    Posts
    39

    if and DateAdd statement returns #Error

    Well I had a try at the if statement and put this in

    Date Due: IIf([Course I]="Epilepsy Awareness & Rescue Medication",DateAdd([Date Completed],'m',12),IIf([Course I]="Epilepsy Awareness & Rescue Med Refesher",DateAdd([Date Completed],'m',14)))

    I get #Error , got something wrong there but can't see what, any ideas appreciated

    Uploading db the above is in the query qryMKaxDateAndOtherStuffLIkeEpilepsy
    Thanks

    nb this is a cut down db of th eone that I am building, with core tables as per my main db
    Attached Files Attached Files

  9. #9
    Join Date
    May 2005
    Location
    Nevada, USA
    Posts
    2,888
    Provided Answers: 6
    Haven't looked at the sample yet, but the DateAdd syntax is:

    DateAdd(interval, number, date)

    You've got

    DateAdd(date,interval,number)
    Paul

  10. #10
    Join Date
    Jul 2009
    Posts
    39
    Thanks Paul , spot on , I copied, or thought I had, the syntax from another DateAdd query that is working, must look back at that.
    Works fine now.
    Many thanks, on to the next task .
    Cheers
    Keith

  11. #11
    Join Date
    May 2005
    Location
    Nevada, USA
    Posts
    2,888
    Provided Answers: 6
    No problem Keith; good luck!
    Paul

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •