Confounded Query Results - Grouping help

[Phoenyxsgirl]

I am running a query to find where an employee worked before leaving the company. I can get the last day they worked which is tied to one specific department using "First" as the grouping sort but I cannot pull the department. When I try to add this information to the query, it brings up all departments.

How do I keep this limited to just the last department they worked in?

[Robeen]

Sort your query by Date Descending and then:
Click View -> SQL View.
Right after the word Select type in Top 1
So your Query will look something like this:

 
SELECT top 1 [TableName].ID, [TableName].[Employee_Name], [TableName].[Department], [TableName].[DateField]
FROM [TableName]
WHERE [TableName].[Employee_Name] = "Some Name"
ORDER BY [TableName].[DateField] DESC;

Hope this helps!!

[Phoenyxsgirl]

When I do this, it takes to top most record only. Not from each employee but from the whole query.

[Robeen]

You said 'an employee'.
I took that to mean a single employee.

[Phoenyxsgirl]

ahhh sorry i have to pull a list of all employees that have left the company. Each employee has multiple worksites they have been at. I have to pull the very last one they were at which I can only distinguish it from the last day they worked at each site.

When I pull the query with just the employee and the last day they worked at ANY site, it gives me the right information.

When I add to the query the name and/or location of the worksite, it then adds in ALL the worksites.

[Robeen]

I've been thinking about it . . .

Here's something you can try:
A.
1. Create a query with EmployeeName and WorkDate fields.
2. Group on EmployeeName and Max on WorkDate.

B.
Create another query with EmployeeName, WorkDate and Department.

C.
1. Create a third query & add the above two queries A & B - in that order.
2. Join the WorkDate fields of the two queries.
3. Right-Click on the Join line and click Properties.
4. Select 2 [ . . . all fields from left table & matching fields from right . . .].
5. Add EmployeeName and WorkDate from query A. and Department from query B.

Does that give you what you need?