"group by" question

[sunrise987]

Hi,

I have a table that keeps track of every change of marital status for
all employees. The table consists of these fields:
EntryNumber, EmployeeID, Status, DateOfUpdate

The information that I am trying to extract from this table is : the
latest "DateOfUpdate" and its corresponding "Status" for each
"EmployeeID".

My strategy for getting this information is:
1. Sort the table by "DateOfUpdate",
2. Group by the "EmployeeID",
3. Then, for each group get the last record.

Here is the query that I used to the above:

SELECT Temp.EmployeeID, Last(Temp.status) AS LastOfstatus, Last(Temp.
[Date of update]) AS [MaxOfDate of update]
FROM (SELECT * FROM [H_Marital Status] ORDER BY [H_Marital Status].
[Date of update]) AS Temp
GROUP BY Temp.EmployeeID;

But the problem is that Access resorts the table by EmployeeID before
doing the aggregation. So the strategy is not working here. I don't
know any other way to doing this. Any help on this matter is greatly
appreciated.

Faiza

 

[John Spencer]

Since your field and table names contain spaces you are going to have to use
multiple queries to solve the problem. Note that I am using aliases for the
table names in some cases to simplify creating the query.

QueryA: Get the date of the last update

SELECT EmployeeID, Max([Date of Update]) as LastUpdate
FROM [H_Marital Status]
GROUP BY EmployeeID

QueryB: Get the details
SELECT B.EmployeeID, B.Status, B.[Date of Update]
FROM [H_Marital Status] as B INNER JOIN QueryA
B.EmployeeID = QueryA.EmployeeID
AND B.[Date of Update] = QueryA.LastUpdate

You could also use a correlated subquery to get the needed information, but
this would be slower, on the other hand you might want an updatable query. So
here is an example of that

SELECT B.EmployeeID, B.Status, B.[Date of Update]
FROM [H_Marital Status] as B
WHERE B.[Date of Update] =
(SELECT Max([Date of Update])
FROM [H_Marital Status] as Tmp
WHERE Tmp.EmployeeID = B.EmployeeID)

John Spencer
Access MVP 2002-2005, 2007-2008
The Hilltop Institute
University of Maryland Baltimore County

 

[Arvi Laanemets]

Hi

One possible solution is to write an UDF, which returns latest marital
status for EmployeeID, and use it in query.

Another way, you can choose, doesn't correspond to best programming
practice, but will work for you perfectly, as long sa you limit it to 1-2
parameters only, is:

1. Split your table.
tblEmployees: EmployeeID, ..., CurrentStatus (you can have some additinal
information like name, birthday, etc. there too)
tblStatuses: ID (or EntryNumber), EmployeeID, Status, DateOfUpdate.

Now design the entry form, where whenever a new status for an employee is
entered, or existing entry is edited, the tblEmployees.CurrentStatus field
is edited by VBA and is replaced with the value from tblStatuses.Status with
latest DateOfUpdate for this employee (I myself would use an UDF again). The
information you are searching for you can display then directly from
tblEmployees without any query.