Using most current employee wage rate

[cmoogy]

Hello,

I am setting up a time tracking database which will track employees maintenance times. Several employees spend X number of hours each day maintaining machines from different categories, such as mowing, snow removal, etc.

I am trying to figure out if it is possible to set the tables up in a way which will not require adding an employees wage rate to every time tracking (listed in the time tracking table below) record. To somehow have the table pull data from the most recent wage record for that particular employee. As employees get raises I would like to retain the old rate in the time tracking table along with the new rate for current data.

The way I have the tables setup right now are as follows.

Employee table

• EmployeeID
• FirstName
• LastName
• Address, Phone, etc

Maintenance Categories table

• MaintID
• MaintName

Wage table

• WageID
• EmployeeID
• DateEffective
• CurrentWage

Time Tracking table

• TimeTrackingID
• EmployeeID (lookup from employee table)
• MaintID (lookup from maintenance table)
• WageID (lookup from the wage table)
• DateOfMaintenance
• StartTime
• EndTime

All of the tables have relationships in the time tracking table.

I will be grateful for any input. Thank you.

[CJ_London]

use a subquery in your criteria- something like

SELECT *
FROM (tblEmployees INNER JOIN tblWages ON tblEmployees.employeeID=tblWages.employeeID) INNER JOIN tblTracking ON tblEmployees.employeeid=tbltracking.employeeid
WHERE tblWages.DateEffective=(SELECT Max(DateEffective) FROM tblWages as T WHERE employeeID=tblWages.employeeID AND DateEffective<tblTracking.DateofMaintenance)

[cmoogy]

Will try that out. thank you.

[June7]

Have to find wage record that has the most recent date for each employee. This gets a little tricky and several ways to accomplish depending on where it needs to happen.

Nested subqueries are one. Using TOP N parameter:
http://allenbrowne.com/subquery-01.html#TopN

Using domain aggregate in query:
SELECT Employee.*, CurrentWage FROM Employees INNER JOIN Wages ON Employees.EmployeeID=Wages.EmployeeID WHERE DateEffective=DMax("DateEffective", "Wages", "EmployeeID=" & [EmployeeID]);

Using domain aggregates in textbox expression:
=DLookup("CurrentWage", "Wages", "EmployeeID=" & [EmployeeID] & " AND DateEffective=#" & DMax("DateEffective", "Wages", "EmployeeID=" & [EmployeeID]) & "#")