Most recent date

  • Thread starter Thread starter Stephanie
  • Start date Start date
S

Stephanie

I'm sorry, I know this topic was kinda covered a couple
days ago, but I didn't quite understand how to solve the
problem.

Our HR department would like to generate a report of
annual review forms that have been distributed but have
not yet been returned by the supervisors. They would like
included in that report the date of the employee's last
review:

Employee ID
(Last Name)
(First Name)
(Middle Name)
Form Distributed Date
Direct Supervisor
Most Recent Completed Review Date


All the information is included in the employee history
table (Employee ID, Form Distributed Date, Date of
Occurrence, etc.).

I can set up a query to bring back all history items where
the form was distributed but not returned. I can set up a
query to bring back all history items where the form was
distributed AND returned, or even all history items
between this and that date where the form was distributed
and returned.

How do I combine the two, or is there even a way to
combine the two, though, to not only bring back the most
recent dates, but to match it to employee?
 
Start with a query that returns each employee's last date

Select [Employee ID], max([Review Date]) as LastReviewed
From tblHistory
Group By [Employee ID]

Save this as qryLastReviewed

Then do your main query

Select <fields from tblHistory>,
qryLastReviewed.LastReviewed
from
tblHistory left join qryLastReviewed on
tblHistory.[Employee ID] = qryLastReviewed.[Employee ID]
WHERE
.....
 
I'm sorry, I've learned Access by trial and error, just
deciding what I want to do and figuring out how to do it -
no official training. I don't understand what you mean.

-----Original Message-----
Start with a query that returns each employee's last date

Select [Employee ID], max([Review Date]) as LastReviewed
From tblHistory
Group By [Employee ID]

Save this as qryLastReviewed

Then do your main query

Select <fields from tblHistory>,
qryLastReviewed.LastReviewed
from
tblHistory left join qryLastReviewed on
tblHistory.[Employee ID] = qryLastReviewed.[Employee ID]
WHERE
.....

-----Original Message-----
I'm sorry, I know this topic was kinda covered a couple
days ago, but I didn't quite understand how to solve the
problem.

Our HR department would like to generate a report of
annual review forms that have been distributed but have
not yet been returned by the supervisors. They would like
included in that report the date of the employee's last
review:

Employee ID
(Last Name)
(First Name)
(Middle Name)
Form Distributed Date
Direct Supervisor
Most Recent Completed Review Date


All the information is included in the employee history
table (Employee ID, Form Distributed Date, Date of
Occurrence, etc.).

I can set up a query to bring back all history items where
the form was distributed but not returned. I can set up a
query to bring back all history items where the form was
distributed AND returned, or even all history items
between this and that date where the form was distributed
and returned.

How do I combine the two, or is there even a way to
combine the two, though, to not only bring back the most
recent dates, but to match it to employee?
.
Click to expand...
.
Click to expand...
 
Back
Top