query that returns first episode

  • Thread starter Thread starter Saul
  • Start date Start date
S

Saul

I have an employee table (tblemployee) related to training
sessions (tblTraining) in a 1:many relationship. Each
employee will have multiple training sessions. I have
written a query that returns the employee and the dates of
the training sessions they have attended. I would like to
write a query that returns the a list of the empoyee names
and the date of the FIRST (or earliest) training session
(only) that each employee attended. Any ideas on how to
do this? Thank-you for your help. It is much appreciated.
 
Saul said:
I would like to
write a query that returns the a list of the empoyee names
and the date of the FIRST (or earliest) training session
(only) that each employee attended.
Click to expand...

Use a Criterion on the datefield of

=(SELECT Min([TrainingDate]) FROM yourtable AS X WHERE X.EmployeeID = yourtable.EmployeeID)
 
try this:

SELECT EmpPrimaryKey, EmpFirstName, EmpLastName, Min(DateFieldName) AS
MinOfDateFieldName
FROM tblemployee INNER JOIN tblTraining ON tblemployee.EmpPrimaryKey =
tblTraining.TrnPrimaryKey
GROUP BY EmpPrimaryKey, EmpFirstName, EmpLastName;

substitute the correct table and field names, of course. note that, with the
exception of the JOIN statement, the field names are not preceded by the
corresponding table names in the above SQL statement. if any of the fields
you use in your query have *the same name*, you will have to specify the
table in the field reference, as

TableName.FieldName

hth
 
Back
Top