Access query nested join/subquery that joins table to a max value based on date in T1

[Bradp]

Access 2007 Windows7

I have done quite a few queries/SQL in the past but am rusty, having not done it for several years.

I have been trying to get the following query to work and have been getting nothing but Syntax errors and other errors.
Several posts lead me to believe that this is possible but at present I have had very limited success in Access.

First table

EmpWhoWorkedOnJobAR

EmpWhoWorkedOnJobAR.TimeRecordID
EmpWhoWorkedOnJobAR.JobID
EmpWhoWorkedOnJobAR.JobNumber
EmpWhoWorkedOnJobAR.JobName
EmpWhoWorkedOnJobAR.EmployeeName
EmpWhoWorkedOnJobAR.EmployeeID
EmpWhoWorkedOnJobAR.DateofWork
EmpWhoWorkedOnJobAR.HoursWorked

Second Table

EmployeeCost

EmployeeCost.IDEmpCost
EmployeeCost.EmployeeID
EmployeeCost.EmployeeLoadedCost
EmployeeCost.EffectiveDate

I need to match the record in the first table to the correct cost in table 2 based on Max EffectiveDate that is EmployeeCost.EffectiveDate<=EmpWhoWorkedOnJobAR.Da teofWork

The following query works to find a max EffectiveDate base on today

SELECT T1.EmployeeID, T1.EffectiveDate, T1.EmployeeLoadedCost
FROM EmployeeCost AS T1 INNER JOIN (SELECT EmployeeID, Max(EffectiveDate) AS CostDate FROM EmployeeCost WHERE EmployeeCost.EffectiveDate<=Date() GROUP BY EmployeeCost.EmployeeID) AS T2 ON (T1.EmployeeID=T2.EmployeeID) AND (T1.EffectiveDate=T2.CostDate);

But the nested join versions I have tried don't work.
Example of latest try

SELECT T.JobID, T.JobNumber, T.JobName, T.EmployeeName, T.EmployeeID, T.DateofWork, T.HoursWorked,
T1.EmployeeID, T1.EffectiveDate, T1.EmployeeLoadedCost
FROM EmpWhoWorkedOnJobAR As T LEFT JOIN (SELECT T1.EmployeeID, T1.EffectiveDate, T1.EmployeeLoadedCost
FROM EmployeeCost WHERE(T1.EmployeeID=(SELECT T2.EmployeeID FROM EmployeeCost as T2 WHERE ( Max(T2.EffectiveDate) <= T.DateofWork ) GROUP BY T2.EmployeeID) As T1) ON T1.EmployeeID=T2.EmployeeID;

Any help appreciated.

[June7]

Why are you saving JobNumber and JobName and EmployeeName as well as ID's in EmpWhoWorkedOnJobAR. Only save the ID's.

Want to provide some data for analysis and testing? Can be just a spreadsheet. Follow instructions at bottom of my post.

[Bradp]

Trying to attach excel files with sample data but this is the first time for me and not seeing the attach works.

[Bradp]

IDEmpCost	EmployeeID	EmployeeLoadedCost	EffectiveDate
40	43	$12.00	10/01/12
39	43	$11.00	10/01/11
38	43	$10.00	10/01/10
36	43	$9.00	10/01/09
3	43	$14.00	10/01/14
41	43	$13.00	10/01/13
4	42	$12.00	10/01/12
5	42	$11.00	10/01/11
6	42	$10.00	10/01/10
7	42	$9.00	10/01/09
8	42	$14.00	10/01/14
9	42	$13.00	10/01/13

[Bradp]

JobID	JobNumber	JobName	EmployeeName	EmployeeID	DateofWork	TimeRecordID	HoursWorked
652	Shop	Shop Projects	A	42	09/30/13	3173	1.5
652	Shop	Shop Projects	A	42	08/20/13	2611	12
676	750	Test Job	A	41	05/31/13	1504	10
676	750	Test Job	A	41	06/03/13	1535	10
676	750	Test Job	B	43	10/24/13	3542	5
652	Shop	Shop Projects	B	43	09/24/13	3541	2
652	Shop	Shop Projects	B	43	09/23/13	3540	3

[Bradp]

Generating reports filtered on JobID for Labor Hours, Labor Cost, Material Cost.

[June7]

This would be easiest if instead of saving EmployeeID, saved IDEmpCost into EmpWhoWorkedOnJobAR. Otherwise, what purpose does IDEmpCost serve?

I often find domain aggregate functions easier than nested query but they can be slow:

SELECT EmpWhoWorkedOnJobAR.*, DLookUp("EmployeeLoadedCost","EmployeeCost","Emplo yeeID=" & [EmployeeID] & " AND [EffectiveDate]=#" & DMax("EffectiveDate","EmployeeCost","EmployeeID=" & [EmployeeID] & " AND EffectiveDate<#" & [DateofWork] & "#") & "#") AS EmpCost
FROM EmpWhoWorkedOnJobAR;

[Bradp]

Thought about that but since I have almost 4000 time records that would need to have the cost entered I would have to write the same query to do that.

EmployeeCost and EmpWhoWorkOnJob are both queries not tables that do some of the initial joining of the normalized tables.
EmployeeCost is tied to Employee reporting about raises and reviews.

This almost works but Access seems to think T.DateofWork is a Parameter Value and I can't seem to get around that.
It does not generate a syntax error which was very frustrating trying to figure out were to put the ().

SELECT T.*, T.DateofWork as [DateofWork], T1.EmployeeLoadedCost
FROM EmpWhoWorkedOnJobAR AS T LEFT JOIN (SELECT T1.IDEmpCost, T1.EmployeeID, T1.EffectiveDate, T1.EmployeeLoadedCost
FROM EmployeeCost AS T1 INNER JOIN (SELECT EmployeeID, Max(EffectiveDate) AS CostDate FROM EmployeeCost WHERE EmployeeCost.EffectiveDate<=T.DateofWork GROUP BY EmployeeCost.EmployeeID) AS T2 ON (T1.EffectiveDate=T2.CostDate) AND (T1.EmployeeID=T2.EmployeeID)) AS Query10 ON T.EmployeeID = Query10.EmployeeID;

[Dal Jeanis]

This should solve your original question:

SELECT
   EJ.TimeRecordID,
   EJ.JobID,
   EJ.JobNumber,
   EJ.JobName,
   EJ.EmployeeName,
   EJ.EmployeeID, 
   EJ.DateofWork,
   EJ.HoursWorked,
   EC.EmployeeLoadedCost,
   EC.EffectiveDate
FROM 
  EmpWhoWorkedOnJobAR AS EJ
  INNER JOIN
  EmployeeCost AS EC
  ON EJ.EmployeeID = EC.EmployeeID
WHERE 
  EC.EffectiveDate = 
   (SELECT MAX(EC2.EffectiveDate)
    FROM EmployeeCost AS EC2
    WHERE EC2.EmployeeID = EJ.EmployeeID
    AND EC2.EffectiveDate <= EJ.DateOfWork);

[Bradp]

what purpose does IDEmpCost serve?

It was put in to be a tie breaker for the Max(EffectiveDate) query in case there were duplicate records in EmployeeCost on EmployeeID and EffectiveDate. Just have to add it into the GROUP BY to reset that.

[Bradp]

Dal Jeanis

Thanks Dal this solves the problem. I tried some WHERE JOINS earlier but kept getting syntax errors.

Thank you Thank you Thank you Thank you Thank you Thank you

[June7]

Yes, need the query to get the field updated and then also modify data entry to save the IDEmpCost on future records.

That query won't work because the inner GROUP BY does not include T in the FROM clause.

Not using domain aggregate functions:

Query1
SELECT EmployeeCost.IDEmpCost, EmployeeCost.EmployeeID, EmployeeCost.EmployeeLoadedCost, EmployeeCost.EffectiveDate, EmpWhoWorkedOnJobAR.DateofWork
FROM EmpWhoWorkedOnJobAR RIGHT JOIN EmployeeCost ON EmpWhoWorkedOnJobAR.EmployeeID = EmployeeCost.EmployeeID
WHERE (((EmployeeCost.EffectiveDate)<[EmpWhoWorkedOnJobAR].[DateofWork]));

Query2
SELECT Query1.*
FROM Query1
WHERE EffectiveDate IN
(SELECT TOP 1 EffectiveDate FROM Query1 AS Dupe WHERE Dupe.EmployeeID=Query1.EmployeeID AND Dupe.DateofWork=Query1.DateofWork AND Dupe.EffectiveDate<Dupe.DateofWork
ORDER BY EmployeeID, Dupe.EffectiveDate DESC);

Query3
SELECT EmpWhoWorkedOnJobAR.*, Query2.EmployeeLoadedCost
FROM Query2 RIGHT JOIN EmpWhoWorkedOnJobAR ON (Query2.DateofWork = EmpWhoWorkedOnJobAR.DateofWork) AND (Query2.EmployeeID = EmpWhoWorkedOnJobAR.EmployeeID);

EDIT: See Dal got a solution while I was testing. Glad you have working solution. As you can see, always more than one way to ...

BTW, Attachment Manager is below the Advanced post editor window. Click Go Advanced at bottom of Quick Reply post editor.