John said:
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)