Eliminate multiple entries in a query (1 Viewer)

[jeffreybrown]

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.

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]);

 

[plog]

The second table lists the supervisor for the employee...
I now need to just show the employee with the last supervisor which is the current supervisor.

From those two sentences you simply need to work in that second table to determine this (using generic field names).

SELECT EmployeeID, MAX(SupervisorID) AS LastSupervisor
FROM SupervisorTable
GROUP BY EmployeeID;

That's it. That query will do it if you have described your tables accurately.

 

[jeffreybrown]

Hi plog and thanks for the reply,

All works well with this query setup; however, it only works if I don't add any other information from the 2nd table other than the SupervisorID.

As soon as I add the Supervisor name it adds back all records instead of just the Last SupervisorID.

 

[plog]

Don't touch that query.

If you need other information related to supervisor or employee, you should build another query using it as a datasource. Let's call the SQL I gave you 'LastSupervisor'. If you want Supervisor name, you would build a new query with LastSupervisor and SupervisorTable and bring all the information you need in there.

 

[jeffreybrown]

Ok that is what I was thinking is the next step...build a second query off of the LastSupervisor query. Thanks so much for the direction. I'll try it now.

 

[jeffreybrown]

Thank you very much, all is sorted out now.