Change source of subform to show all dates or just most recent

[Craig]

Hello,

I am designing a database to hold employee training information in Microsoft
Access 2003.

I have a form (frmTraining) with a subform (frmTI). The main form shows the
employee's information: Name, number, shift, etc. The subform shows all of
the training courses that the employee has participated in, along with the
date he/she completed the course and a field for comments.

I want the user to be able to click a button or a checkbox, which will
activate some Visual Basic code that changes the source of the subform --- so
that the user can either view ALL of the employee's training records, or just
the most recent training record for each course.

I can figure out how to show all the records (simply set the source to
tblTrainingIncidents), and I can figure out how to show only the most recent
record in a query (qryTI, based on tblTrainingIncidents, which uses a
MaxOfDate field).

My question: Is there an elegant way to switch between the two sources? Or
do I need to come up with an alternative for the source when I want to show
only the most recent training record?

I apologize if this is not very clear --- any help is greatly appreciated!

Thanks,

Craig

 

[KARL DEWEY]

Post your query that uses MaxOfDate field.

 

[Craig]

I am not sure if this is what you mean by posting the query, but here is the
SQL statement. This was automatically generated by Access --- I created the
query in Design View:

SELECT tblAssociates.AssocNo, tblTrainingIncidents.CourseNo,
Max(tblTrainingIncidents.Date) AS MaxOfDate, tblTrainingCourses.Category
FROM (tblAssociates INNER JOIN tblTrainingIncidents ON tblAssociates.AssocNo
= tblTrainingIncidents.AssocNo) INNER JOIN tblTrainingCourses ON
tblTrainingIncidents.CourseNo = tblTrainingCourses.CourseNo
GROUP BY tblAssociates.AssocNo, tblTrainingIncidents.CourseNo,
tblTrainingCourses.Category;

There are three tables behind the query: tblAssociates, which contains a
list of employees; tblTrainingCourses, which contains a list of courses; and
tblTrainingIncidents, which contains a list of training "incidents" --- times
when an associate took a course. Each associate and each course has a
distinct number.

Thanks,

Craig