Return last record for employee

[Sabosis]

Hello-

I am having trouble narrowing down my select query results to only
show the last instance by date for each employee. I am trying to
return the last write up for each employee.

EmpName WUDate WUType WULevel
Joe Smith 09/25/08 Attendance 1
Joe Smith 10/15/08 Attendance 2
Josh Wilson 08/14/08 Attendance 1
Josh Wilson 10/25/08 Attendance 2
Josh Wilson 11/10/08 Attendance 3

What I want is to only return the level 2 entry for Joe Smith, and the
level 3 entry for Josh Wilson. How is this done?

 

[Clifford Bass]

Hi Sabosis,

One way, tell Access to give you all records where there is no later
(by date) record for the same person:

select *
from tblWriteUps as A
where not exists
(select * from tblWriteUps as B
where B.EmpName = A.EmpName and B.WUDate > A.WUDate);

Or, if you wanted the most recent writeup of each writeup type:

select *
from tblWriteUps as A
where not exists
(select * from tblWriteUps as B
where B.EmpName = A.EmpName and B.WUType = A.WUTYPE and B.WUDate > A.WUDate);

Clifford Bass

 

[John W. Vinson]

Hello-

I am having trouble narrowing down my select query results to only
show the last instance by date for each employee. I am trying to
return the last write up for each employee.

EmpName WUDate WUType WULevel
Joe Smith 09/25/08 Attendance 1
Joe Smith 10/15/08 Attendance 2
Josh Wilson 08/14/08 Attendance 1
Josh Wilson 10/25/08 Attendance 2
Josh Wilson 11/10/08 Attendance 3

What I want is to only return the level 2 entry for Joe Smith, and the
level 3 entry for Josh Wilson. How is this done?

If you (unwisely! names are not unique!) use your EmpName to identify the
employee rather than a unique EmployeeID, put a criterion on WUDate of

=(SELECT Max([WUDate]) FROM yourtablename AS X WHERE X.EmpName =
yourtablename.EmpName)

Use the unique EmployeeID rather than EmpName if you have one.

 

[Sabosis]

Thank you, that worked! My next problem is a bit more complex. I am
trying to use the date that the query turns up as a parameter to find
the employees who a further write up. I have a table for Attendance
(tblAttendance) that keeps records on late starts, late lunches, etc
(different types of attendance infractions). Ultimately what I wanted
to do was to use the date of the last occurance as a sort of parameter
(for each employee that turns up in the query above), then summarize
the count of attendance infractions since that date to see if the
employees in the above query need to move to the next level of write
ups. The trick I cant figure out is that each employee will have a
different date to use as the starting point. In VBA, this would almost
be like a looping situation, I have no idea if access can do this.
Please let me know if you have any ideas.

Thanks a million!

Scott

 

[John W. Vinson]

Thank you, that worked! My next problem is a bit more complex. I am
trying to use the date that the query turns up as a parameter to find
the employees who a further write up. I have a table for Attendance
(tblAttendance) that keeps records on late starts, late lunches, etc
(different types of attendance infractions). Ultimately what I wanted
to do was to use the date of the last occurance as a sort of parameter
(for each employee that turns up in the query above), then summarize
the count of attendance infractions since that date to see if the
employees in the above query need to move to the next level of write
ups. The trick I cant figure out is that each employee will have a
different date to use as the starting point. In VBA, this would almost
be like a looping situation, I have no idea if access can do this.
Please let me know if you have any ideas.

Thanks a million!

Scott

I'm leaving this morning for a week and won't have reliable internet
connection - I'd suggest you start a new thread to see if someone else can
help. Sorry!

 

[Sabosis]

I'm leaving this morning for a week and won't have reliable internet
connection - I'd suggest you start a new thread to see if someone else can
help. Sorry!
--

             John W. Vinson [MVP]- Hide quoted text -

- Hide quoted text -

Thanks John, I appreciate the help I have received from you in the
past. I will wait to see if someone replies to this post, if not I
will try it again later. At this point, I am just wondering if this
type of thing is at all possible.... Have a good trip!

Scott

 

[Clifford Bass]

Hi Scott,

Umm... If you have/use the last infraction date, then there will never
be any further infractions after that date. So your answer will always be
zero for everyone.

Clifford Bass

 

[Sabosis]

Hi Scott,

      Umm... If you have/use the last infraction date, then there will never
be any further infractions after that date.  So your answer will alwaysbe
zero for everyone.

                    Clifford Bass







- Show quoted text -

Clifford-

I have the following information from another post that may help to
explain this better.
I have a query which will indicate any employee on an active write up
in the last six months. What I need to do, based on the example
below,
is to use each employees WU (write up) date as the "start date" for
their
individual record in a new query. The number that is represented in
the LateStarts is the number since they started employment, but I
need
that number to actually be the number of late starts since their last
write up. I want to
know the number of Late starts since the WUDate to see if they need
to
move on to another write up. I dont know how to use the WUDate field
as a starting point for each person though. Please advise if you can
help. Thanks


EmplID Last Name Late Starts WUDate
150 Jones 1
9/5/2008
193 Harris 20
7/15/2008
154 Williams 2 11/17/2008
196 Flores 2
7/10/2008
204 Hansen 8 11/14/2008
177 Walters 30 10/30/2008

 

[Clifford Bass]

Hi Scott,

I think I get it. Employees infractions are being recorded and
separately there may or may not be writeups of those infractions. Tell me
the tables and columns involved, then I can either give you the solution or
point you towards it.

Clifford Bass

 

[Sabosis]

Hi Scott,

     I think I get it.  Employees infractions are being recorded and
separately there may or may not be writeups of those infractions.  Tellme
the tables and columns involved, then I can either give you the solution or
point you towards it.

             Clifford Bass            






- Hide quoted text -

Clifford-

Here is the sql for the query that will show the last write up date
for any reps that have a write up in the last 6 months (filtered in
qryAttendanceCorrective). The sum for late starts that is shown here
is the total since they have been employed, what I need to show is the
sum since the last CorrectiveDate for each employee.

SELECT tblEmployees.EmplID, tblEmployees.EmpLName, -Sum
([tblAttendance].LS) AS [Late Starts],
qryAttendanceCorrective.CorrectiveDate
FROM (qryAttendanceCorrective INNER JOIN tblEmployees ON
qryAttendanceCorrective.tblEmployees.EmplID = tblEmployees.EmplID)
INNER JOIN tblAttendance ON tblEmployees.EmplID = tblAttendance.EmplID
WHERE (((tblEmployees.EmpTermDate) Is Null))
GROUP BY tblEmployees.EmplID, tblEmployees.EmpFName,
tblEmployees.EmpLName, qryAttendanceCorrective.tblCorrective.EmplID,
qryAttendanceCorrective.CorrectiveDate
ORDER BY tblEmployees.EmpLName;

I appreciate your help on this, it has been driving me nuts

 

[Clifford Bass]

Hi Scott,

I think you are almost there. Try adding the following to your where
clause.

and [tblAttendance].[InfractionDate] >
[qryAttendanceCorrective].[CorrectiveDate]

You will need to adjust the InfractionDate column to the appropriate
name.

If that does not do it, let me know.

Clifford Bass

 

[Sabosis]

Hi Scott,

     I think you are almost there. Try adding the following to yourwhere
clause.

and [tblAttendance].[InfractionDate] >
[qryAttendanceCorrective].[CorrectiveDate]

     You will need to adjust the InfractionDate column to the appropriate
name.

     If that does not do it, let me know.

                             Clifford Bass

Clifford-

Here is the sql for the query that will show the last write up date
for any reps that have a write up in the last 6 months (filtered in
qryAttendanceCorrective). The sum for late starts that is shown here
is the total since they have been employed, what I need to show is the
sum since the last CorrectiveDate for each employee.

SELECT tblEmployees.EmplID, tblEmployees.EmpLName, -Sum
([tblAttendance].LS) AS [Late Starts],
qryAttendanceCorrective.CorrectiveDate
FROM (qryAttendanceCorrective INNER JOIN tblEmployees ON
qryAttendanceCorrective.tblEmployees.EmplID = tblEmployees.EmplID)
INNER JOIN tblAttendance ON tblEmployees.EmplID = tblAttendance.EmplID
WHERE (((tblEmployees.EmpTermDate) Is Null))
GROUP BY tblEmployees.EmplID, tblEmployees.EmpFName,
tblEmployees.EmpLName, qryAttendanceCorrective.tblCorrective.EmplID,
qryAttendanceCorrective.CorrectiveDate
ORDER BY tblEmployees.EmpLName;

I appreciate your help on this, it has been driving me nuts- Hide quoted text -

- Hide quoted text -

Clifford, that did it! A wholehearted "thank you" for helping along
with this. I still have work to do, but it should be downhill from
here. Your assistance has been a great benefit to myself and my
managment team.

Happy Holidays!

Scott

 

[Clifford Bass]

Hi Scott,

Glad to help. And a happy holidays to you too!

Clifford Bass