last date in sub form

[lynn.atkinson]

I have a subform based on a query which displays courses
attended by employees, date of attendance and the date a
refresher is due. I want to display this on a training
record for each employee which I have achieved by linking
the employee id as the link fields and this works great.
However, it displays all the courses the person has ever
been on. I would like to display only the latest
attendance to a particular course.
For example, First Aid has to be attended every 12 months.
I only want to display the LAST First Aid course attended
and not ALL the First Aid courses ever attended by that
person.
How do I just display the last course in this subform?

Hope this explains my query clearly enough!
regards

 

[Allen Browne]

Hi Lynn

You will need the right data structure to make this work. Because you are
repeating courses, you should have tables like this:
- tblEmployee 1 record for each employee.
- tblCourse 1 record for each possible course.
- tblCourseInstance 1 record for each time a course is run
- tblEnrol 1 record for each Employee in a CourseInstance.

Now you can make a query that displays only the most recent course the
person has taken, and make it the RecordSource for your subform.

1. Create a query into your table.

2. Depress the Total button on the toolbar (Upper sigma icon).
Access adds a Total row to the grid.

3. Drag EmployeeID into the grid. Accept Group By in the Total row.

4. Drag the CourseID into the grid, and accept Group By.

5. Drag the CourseInstanceDate field into the grid.
In the Total row under this field, choose Max.
This gives you the most recent date for the combination of Employee and
CourseInstance.

6. Save the query.

7. Open your subform in design view, and choose this query as its
RecordSource.