jeffreybrown
Registered User.
- Local time
- Today, 08:20
- Joined
- Aug 13, 2010
- Messages
- 15
Not sure my title spell out exactly what I'm looking to do but I'll try to add some more information.
If have a union query between two tables.
The first table lists employees and contains a key ID field. Basically, one employee, one record.
The second table lists the supervisor for the employee. One employee can have multiple supervisor and this table also has a key ID field. The last supervisor added to the database will have the greater numerical key ID field.
In the union query, I've used both of these tables to retrieve a list of employees and their supervisors and then constructed a report.
What I need to do now is run this same type of query, but instead of returning all the supervisors associated to one employee, I now need to just show the employee with the last supervisor which is the current supervisor.
Example: Employee with record #1 has two supervisors: SupervisorID 1 and 192.
I need the query to only show the record with SupervisorID 192.
Here is the SQL if that helps.
If have a union query between two tables.
The first table lists employees and contains a key ID field. Basically, one employee, one record.
The second table lists the supervisor for the employee. One employee can have multiple supervisor and this table also has a key ID field. The last supervisor added to the database will have the greater numerical key ID field.
In the union query, I've used both of these tables to retrieve a list of employees and their supervisors and then constructed a report.
What I need to do now is run this same type of query, but instead of returning all the supervisors associated to one employee, I now need to just show the employee with the last supervisor which is the current supervisor.
Example: Employee with record #1 has two supervisors: SupervisorID 1 and 192.
I need the query to only show the record with SupervisorID 192.
Here is the SQL if that helps.
SELECT DISTINCTROW Force_Support_T.[Name Employee], Force_Support_T.[PP-SRS-GR], Force_Support_T.[Intern Projected Grad Date], Supervisor_T.Rotation, Supervisor_T.[Supervisor Name], Supervisor_T.[Supervisor Email], Supervisor_T.[Supervisor DSN], Last(Supervisor_T.[Supervisor As of Date]) AS [LastOfSupervisor As of Date], Force_Support_T.[Outplacement Date], Force_Support_T.[Date Hired]
FROM Force_Support_T INNER JOIN Supervisor_T ON Force_Support_T.ID = Supervisor_T.ID
GROUP BY Force_Support_T.[Name Employee], Force_Support_T.[PP-SRS-GR], Force_Support_T.[Intern Projected Grad Date], Supervisor_T.Rotation, Supervisor_T.[Supervisor Name], Supervisor_T.[Supervisor Email], Supervisor_T.[Supervisor DSN], Force_Support_T.[Outplacement Date], Force_Support_T.[Date Hired], Force_Support_T.[Centrally Managed Posn Type Desc], Force_Support_T.ID, Force_Support_T.[Off Rolls Date]
HAVING (((Force_Support_T.[Centrally Managed Posn Type Desc])="Pal Acq (PAQ) Int Posn") AND ((Force_Support_T.[Off Rolls Date]) Is Null))
ORDER BY Force_Support_T.[Centrally Managed Posn Type Desc], Force_Support_T.[Name Employee], Force_Support_T.[Intern Projected Grad Date], Last(Supervisor_T.[Supervisor As of Date]);