Query to show relevant training

[E.Q.]

I want to make a report (which I hope to eventually use as a subreport) to
detail training classes employees have taken since they last renewed
licenses. Training only counts if it's occurred after the issue date of the
certificate.
I have four tables that I believe would be relevant, a truncated list follows:

tblTrainingClass:
lngClassID (PK)
chrClassTitle
dtmClassDate
....

tblEmployee:
chrEmpID (PK)
chrLName
chrFName
....
tblTrainee: (an intersection table)
lngClassID (PK)
chrEmpID (PK)

tblCertificate:
lngCertificateID (PK)
chrEmpID (FK)
dtmIssueDate
dtmExpirationDate
....
I would like to grab the earliest issue date (dtmIssueDate) of any
certificate held by an employee (selected using a form) and use that as a
condition limiting which training classes the employee has attended.
(dtmClassDate)
For example, if an employee holds two certificates with the earlier issue
date being Jan 15, 2008. I would like to generate a report showing all
courses taken since that date by that employee.
I'd like to figure this out first, but then, if possible, I'd like for that
report to be a subreport on an existing report detailing info regarding
certificates held by a selected employee. (I've never used subreports, but
I'm hoping that the chrEmpID field would play the parent-child linking role
as it would if I were working with a subform.)
But before I worry about subreports, I need to understand how to get the
MIN(dtmIssueDate) from an employee's records in tblCertificate to select
desired records from tblTrainingClass.
Thank you
EQC

 

[Daryl S]

E.Q. -

Create your report as if you didn't care about the date criteria. That is,
add the tables and fields you want in the report. Add the joins if
necessary. Test it out. Then in the Criteria column under the dtmClassDate
field, enter the following restriction:

(select min(dtmIssueDate) from tblCertificate where tblCertificate.chrEmpID = tblEmployee.chrEmpID)

That will only show the classes taken since that employee's minimum
certificate date. Test this out.

Note that if some employees have no certifications or no training after a
certification, then you will need to union this result with a query looking
for all training taken by employees without certifications, and possibly
union that with employees without any training records.

 

[E.Q.]

I created the report as you suggested but couldn't quite get the query to
work. I made a new saved query to gleen all the issue dates for the selected
employee and used the ">DMIN()" funcion on that query as the criterion for
the dates in the training dates.... Not exactly as you wrote, but you
definitely pointed me in the right direction and the report works as I had
hoped.
Thank you.

 

[Daryl S]

There's always some tweaking to do, but glad it worked!