Results 1 to 3 of 3
  1. #1
    Lounger
    Join Date
    Jan 2001
    Location
    Victoria, Texas, USA
    Posts
    28
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Pulling data from a Training Database (2002)

    I have a database where I track the training of the employees in my company. I need to be able to pull out the records of the last time employees took the different training classes for a report.. So far I haven't been able to pull out just the last occurance.

    There are two tables, one for employee information and a second where the training classes are stored. The training table has 6 columns; Date, TrainingID, Instructor, SSN, TrainingDescription, and CreditHours. The SSN ties the Training table to the employee table.

    Any help will be greatly appreciated.

    Thanks
    Jim

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

    Re: Pulling data from a Training Database (2002)

    Start by creating a query based on the training table.
    Add the SSN and Date fields to the query grid.
    Select View | Totals or click the Totals button on the toolbar to change the query into a Totals query.
    Set the Total option for the Date field to Max (the one for SSN remains the default Group By).
    This query returns the most recent training date for each employee.
    (If you want the most recent date for each employee for each course, add the TrainingID field to the query grid, with Group By as Total option).
    Save this query as qryMaxDate.

    Next, create a new query based on qryMaxDate, the training table, and if you need employee info, the employee table.
    Join qryMaxDate and the training table on SSN and on Date vs MaxOfDate (and if you added TrainingID, on that field too).
    Join the training table to the employee table on SSN.
    Add the fields you need.
    The resulting query will not be updatable, but it can be used as record source for a report.

  3. #3
    Lounger
    Join Date
    Jan 2001
    Location
    Victoria, Texas, USA
    Posts
    28
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Pulling data from a Training Database (2002)

    Thanks for the information, it got me started down the right road with a problem I had. Sorry it took me so long to respond.

    Jim

Posting Permissions

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