Aggregate Functions & Group By Fields

[George B via AccessMonster.com]

I am trying to create an active employee list.

Essential table structure is as follows:
tbEvents (a table holding all employee and applicant actions)
-pkeventid (primary key - autonumber)
-date (date of the action)
-fkeventtypeid (foreign key of the action type e.g. promotion, transfer,
flogging)
-fkpeopleid (foreign key of the employee)

tbEvents has thousands of records. To create an active employee list, I need
a query that can go through the table and select all records reflecting the
hiring of an employee. Some employees have been hired, left and been rehired.
I only want the record of their most recent hiring. Then, I need to
eliminate from that set any employees with a termination date later than
their most recent hire date.

I am having quite a bit of trouble and any advice on structuring the sql
would be greatly appreciated.

Thanks,
g

 

[John Spencer]

One way, might be the untested SQL

SELECT fkPeopleID
FROM tbEvents
WHERE [Date] =
(SELECT Max(T.[Date])
FROM tbEvents as T
WHERE T.fkPeopleID = tbEvents.fkPeopleID
And fkEventTypeID = "Hired")
AND NOT EXISTS
(SELECT *
FROM tbEvents as E
WHERE E.fkPeopleID=tbEvents.fkPeopleID
AND E.fkEventTypeID="Termination"
AND E.Date > (SELECT Max(T.[Date])
FROM tbEvents as T
WHERE T.fkPeopleID = tbEvents.fkPeopleID
And fkEventTypeID = "Hired"))

There is undoubtedly a better way to write that.

 

[George B via AccessMonster.com]

Thanks for your recommendation. I tried it out, but the results aren't right.

Specifically, when I run the first section:

SELECT fkPeopleID
FROM tbEvents
WHERE [Date] =
(SELECT Max(T.[Date])
FROM tbEvents as T
WHERE T.fkPeopleID = tbEvents.fkPeopleID
And fkEventTypeID = "Hired")

I get numerous events other than hired. It's as if that restriction isn't
catching.
Any ideas?

George

One way, might be the untested SQL

SELECT fkPeopleID
FROM tbEvents
WHERE [Date] =
(SELECT Max(T.[Date])
FROM tbEvents as T
WHERE T.fkPeopleID = tbEvents.fkPeopleID
And fkEventTypeID = "Hired")
AND NOT EXISTS
(SELECT *
FROM tbEvents as E
WHERE E.fkPeopleID=tbEvents.fkPeopleID
AND E.fkEventTypeID="Termination"
AND E.Date > (SELECT Max(T.[Date])
FROM tbEvents as T
WHERE T.fkPeopleID = tbEvents.fkPeopleID
And fkEventTypeID = "Hired"))

There is undoubtedly a better way to write that.

I am trying to create an active employee list.

[quoted text clipped - 21 lines]

Thanks,
g

 

[John Spencer]

Should have added fkEventTypeID = "Hired" to the Outer query
I still think there is a better way to write this, I'm just not seeing it
right now.

SELECT fkPeopleID
FROM tbEvents
WHERE fkEventTypeID = "Hired" AND
[Date] =
(SELECT Max(T.[Date])
FROM tbEvents as T
WHERE T.fkPeopleID = tbEvents.fkPeopleID
And fkEventTypeID = "Hired")
AND NOT EXISTS
(SELECT *
FROM tbEvents as E
WHERE E.fkPeopleID=tbEvents.fkPeopleID
AND E.fkEventTypeID="Termination"
AND E.Date > (SELECT Max(T.[Date])
FROM tbEvents as T
WHERE T.fkPeopleID = tbEvents.fkPeopleID
And fkEventTypeID = "Hired")

Thanks for your recommendation. I tried it out, but the results aren't
right.

Specifically, when I run the first section:

SELECT fkPeopleID
FROM tbEvents
WHERE [Date] =
(SELECT Max(T.[Date])
FROM tbEvents as T
WHERE T.fkPeopleID = tbEvents.fkPeopleID
And fkEventTypeID = "Hired")

I get numerous events other than hired. It's as if that restriction isn't
catching.
Any ideas?

George

One way, might be the untested SQL

SELECT fkPeopleID
FROM tbEvents
WHERE [Date] =
(SELECT Max(T.[Date])
FROM tbEvents as T
WHERE T.fkPeopleID = tbEvents.fkPeopleID
And fkEventTypeID = "Hired")
AND NOT EXISTS
(SELECT *
FROM tbEvents as E
WHERE E.fkPeopleID=tbEvents.fkPeopleID
AND E.fkEventTypeID="Termination"
AND E.Date > (SELECT Max(T.[Date])
FROM tbEvents as T
WHERE T.fkPeopleID = tbEvents.fkPeopleID
And fkEventTypeID = "Hired"))

There is undoubtedly a better way to write that.

I am trying to create an active employee list.

[quoted text clipped - 21 lines]

Thanks,
g