Get the value with the highest ID number

[John]

Hi,

I have a table with for instance these 2 values:
ID Employee Department
1 Jones, M. Frontoffice
2 Jones, M. Backoffice

In a combobox the user types-ahead the name. When they choose a name the
latest entered department name (the one with the highest ID-number) should
also be entered in the department field. In the example that is the
Backoffice. I'm planning to do this with code in the after-update event.

Is it possible to use Dlookup for this?
If that's not possible and I would have to use a query, how would I get the
right deparment value of the record that has the highest ID number for a
particular employee?

Thank you.
john

 

[Arvin Meyer [MVP]]

Try a query:

SELECT Max(ID) AS MaxID, Employee
FROM YourTable
GROUP BY Employee;

Then run a second query on the same table that adds the Department.

 

[John]

Thanks. I was thinking...can I somehow combine those queries into one
SQL-statement and put that in the rowsource of the combo? How would I go
about that?
John

 

[James A. Fortune]

Thanks. I was thinking...can I somehow combine those queries into one
SQL-statement and put that in the rowsource of the combo? How would I go
about that?

tblEmployeeLocations
EmployeeLocationID AutoNumber (Primary Key)
EmployeeID Long (Foreign Key)
DepartmentID (Foreign Key)
EmployeeLocationID EmployeeID DepartmentID
EffectiveDate Date/Time
1 1 1 3/15/2007
2 1 2 2/2/2008
3 2 2 5/5/2006
4 2 1 2/2/2008

tblEmployees
EmployeeID AutoNumber (Primary Key)
EmployeeLastName Text
EmployeeFirstName Text
EmployeeID EmployeeLastName EmployeeFirstName
1 Jones M.
2 Smith J.

tblDepartments
DepartmentID AutoNumber (Primary Key)
DepartmentName Text
DepartmentLocation Text
DepartmentID DepartmentName DepartmentLocation
1 Frontoffice FrontOffice
2 Backoffice BackOffice

With those changes in place, tblEmployeeLocations maintains a history of
each department where each employee worked. Thus, the maximum
EmployeeLocationID for each employee corresponds to their current location.

qryCurrentEmployeeDepartments:
SELECT EmployeeLocationID, EmployeeLastName & ", " & EmployeeFirstName
AS Employee, DepartmentName FROM (tblEmployeeLocations INNER JOIN
tblEmployees ON tblEmployeeLocations.EmployeeID =
tblEmployees.EmployeeID) INNER JOIN tblDepartments ON
tblEmployeeLocations.DepartmentID = tblDepartments.DepartmentID WHERE
EmployeeLocationID = (SELECT Max(A.EmployeeLocationID) FROM
tblEmployeeLocations AS A WHERE A.EmployeeID =
tblEmployeeLocations.EmployeeID);

!qryCurrentEmployeeDepartments:
EmployeeLocationID Employee DepartmentName
2 Jones, M. BackOffice
4 Smith, J. FrontOffice

The DMax function can be used in place of the subquery, or even DLookup:

SELECT EmployeeLocationID, EmployeeLastName & ", " & EmployeeFirstName
AS Employee, DepartmentName FROM (tblEmployeeLocations INNER JOIN
tblEmployees ON tblEmployeeLocations.EmployeeID =
tblEmployees.EmployeeID) INNER JOIN tblDepartments ON
tblEmployeeLocations.DepartmentID = tblDepartments.DepartmentID WHERE
EmployeeLocationID = DMax("EmployeeLocationID", "tblEmployeeLocations",
"EmployeeID = " & CStr(tblEmployeeLocations.EmployeeID));

SELECT EmployeeLocationID, EmployeeLastName & ", " & EmployeeFirstName
AS Employee, DepartmentName FROM (tblEmployeeLocations INNER JOIN
tblEmployees ON tblEmployeeLocations.EmployeeID =
tblEmployees.EmployeeID) INNER JOIN tblDepartments ON
tblEmployeeLocations.DepartmentID = tblDepartments.DepartmentID WHERE
EmployeeLocationID = DLookup("Max(EmployeeLocationID)",
"tblEmployeeLocations", "EmployeeID = " &
CStr(tblEmployeeLocations.EmployeeID));

I would set the combobox's Column Count to three and use Column Widths
to hide the EmployeeLocationID.

Now that I see how the combobox is being used, perhaps use:

qryCurrentEmployeeDepartments:
SELECT tblEmployeeLocations.EmployeeID, EmployeeLastName & ", " &
EmployeeFirstName AS Employee, DepartmentName FROM (tblEmployeeLocations
INNER JOIN tblEmployees ON tblEmployeeLocations.EmployeeID =
tblEmployees.EmployeeID) INNER JOIN tblDepartments ON
tblEmployeeLocations.DepartmentID = tblDepartments.DepartmentID WHERE
EmployeeLocationID = (SELECT Max(A.EmployeeLocationID) FROM
tblEmployeeLocations AS A WHERE A.EmployeeID =
tblEmployeeLocations.EmployeeID);

so that when an employee is selected from the combobox you have the
EmployeeID as the bound field. You can use the combobox's Column
property (0 based) to obtain the department corresponding to the
employee selected, in your AfterUpdate code to populate a current
department field on the form. That would also allow you to get rid of
the EmployeeLocationID AutoNumber in preference of natural key fields if
desired .

Also, in retrospect, why not use the EffectiveDate rather than the
EmployeeLocationID to find the most recent location? I like that idea
better.

James A. Fortune
(e-mail address removed)

 

[John]

Thanks for the pointers. For now I stick with a less normalized database.
Changing that would take too much time now. But I adopted your idea of
taking the EffectiveDate rather than the ID number.
John

 

[Arvin Meyer [MVP]]

ID's are always unique, dates may, or may not be, depending upon conditions.
In most cases, using 2 queries will achieve better performance than either a
subquery, or Domain Aggregate functions. Using 2 queries is not as
conventional as using a subquery, but it allows use of specific indexes and
processing blocks of indexed data, rather than processing a row at a time.
With a small amount of data, performance improvement is miniscule, but on
large amounts of data, the time savings can be significant.

 

[James A. Fortune]

ID's are always unique, dates may, or may not be, depending upon conditions.
In most cases, using 2 queries will achieve better performance than either a
subquery, or Domain Aggregate functions. Using 2 queries is not as
conventional as using a subquery, but it allows use of specific indexes and
processing blocks of indexed data, rather than processing a row at a time.
With a small amount of data, performance improvement is miniscule, but on
large amounts of data, the time savings can be significant.

Arvin,

For a table that handles employee moves from department to department I
think the date will do fine. Maybe force an edit if the same employee
moves multiple times in the same day or include a timestamp so that each
move is tracked even if it happens on the same day as another move. I
agree that two queries are often better than one . A saved or an
unsaved query based on a saved query would work in the combobox.

James A. Fortune
(e-mail address removed)