Results 1 to 2 of 2
  1. #1
    3 Star Lounger
    Join Date
    Jan 2001
    Location
    St. Marys, Ontario, Canada
    Posts
    270
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Most recently updated date (2002)

    I have employees taking machinery safety courses. Each machine has updates on various safety features. These dates will usually be different. Each time one of the dates is changed, the employees have to take updated training. I have the course date in one table that is connected to the table with all of the machinery and updates. What I want to be able to do is to have access show me the employee, when he took the last course and when the last update was done on any of the safety features. In other words, I want to create a query that returns only the most recent date of a safety feature update so it can be compared with the last training date of the employee to see if updated training needs to take place. I don't want to see all of the updates on safety features, only the most recent one.

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Most recently updated date (2002)

    Create a new query based on the machinery table.
    Add the relevant date field to the query grid.
    Select View | Totals or click the Totals button on the toolbar.
    Change the Total option for the date field from 'Group By' to 'Max'.
    Save this query as qryLastUpdate. It returns a single value: the most recent date of an update in the machinery table.

    Next, create a new query based on the course table.
    Add the employee ID field and the course date field.
    Select View | Totals or click the Totals button on the toolbar.
    Change the Total option for the course date field from 'Group By' to 'Max'.
    Save this query as qryLastCourse. It returns the most recent course date for each employee.

    Finally, create a new query based on qryLastCourse and qryLastUpdate.
    There is no need to join the queries.
    Add all fields from both queries to the query grid.
    Switch to datasheet view.

Posting Permissions

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