Query for most recent record

[Icon]

I have a table with the following fields:

ID EE Name Date Effective Status Start Date End Date
1 John Smith 1/1/2005 Part Time 1/1/2005 8/20/2009
1 John Smith 8/21/2009 Full Time 8/21/2009 8/21/2010

I'm looking for the most recent status of each employee. Some have only one entry others have mutiple entries. But I'm looking for the record with the latest effective date. In the case above my results should only include the record for John Smith with a status of Full Time since it has the latest effective date.

I'm using a select query with a group by clause to elimate duplicate records. I also use the "Last" criteria for the effective date but for every employee that has more than one record and has a different status type I get multiple records back. Like the case above.



Does anyone know how to eliminate the duplicate record in this type fo query? Thanks.

[amrut]

Try Max instead of last in the effective date field.

[Icon]

Max has the same effect on the date column. It give me the latest date but I still get a record for each status type.

[amrut]

Can you post SQL of your query ?

[amrut]

Here is a workaround -
Insert a column in your query having expression - IIf([EffectiveDate]=DMax("EffectiveDate","YourTable","[EmployeeName]='" & [EmployeeName] & "'"),1,0) and criteria as 1.

[Icon]

SELECT Today.[Employee ID], Today.[Employee Full Name], Last(Today.[Date Effective]) AS [Date Effective], Today.[Work Status], Last(Today.[Begin Date]) AS [Begin Date], Last(Today.[End Date]) AS [End Date],
FROM Today
GROUP BY Today.[Employee ID], Today.[Employee Full Name], Today.[Work Status]
ORDER BY Today.[Employee ID], Last(Today.[Date Effective]);

[amrut]

Try -

SELECT Today.[Employee ID], Today.[Employee Full Name], Today.[Date  Effective], Today.[Work Status], Today.[Begin  Date],Today.[End Date], IIf([Date Effective]=DMax("Date Effective","Today","[Employee ID]=" & [EmployeeID]),1,0) as CheckDate
FROM Today WHERE IIf([Date Effective]=DMax("Date Effective","Today","[Employee ID]=" & [Employee ID]),1,0)=1;

does above works ?
If employee ID is a text field, you have to use "[Employee ID]='" & [Employee ID] & "'" in the criteria part of dmax.
Further, experts advise to avoid spaces in field names. Ex. instead of Employee ID, use EmployeeID

[Icon]

Here is a workaround -
Insert a column in your query having expression - IIf([EffectiveDate]=DMax("EffectiveDate","YourTable","[EmployeeName]='" & [EmployeeName] & "'"),1,0) and criteria as 1.

I tried adding the column as writter but all I get back is #Error in each record. Does this work with both SQL view and Design View?

[amrut]

Try in design view and without criteria and remove the "Totals" (group by)
If employee ID is not a text field, you have to use "[Employee ID]=" & [Employee ID] in the criteria part of dmax.
Once you get 0 or 1 in this column, set 1 as criteria

[Icon]

Try in design view and without criteria and remove the "Totals" (group by)
If employee ID is not a text field, you have to use "[Employee ID]=" & [Employee ID] in the criteria part of dmax.
Once you get 0 or 1 in this column, set 1 as criteria

First, I removed the "Totals" group by clause. The Employee ID field is text so I used your second suggestion.

I tried running the query without the where clause and I get back results but every cell is populated by #Error.

When I added the where clause to the query I get the error "Syntax error (missing operator) in query expression 'Max(Date Effective)'.

[amrut]

There must be some error in the DMax syntax resulting in the error, check your field names too. Can you post your db, with only this table in question ?