I have a tblJobs whose ID is JobID,

[karassik]

I have a tblJobs whose ID is JobID, then I have a tblRemainingHours that is in a many relationship to the tblJobs. Every week I will be entering new data into the tblRemainingHours.

I want to sort tblJobs for CurrentJobs=True and for each of these jobs I need to the the latest date and corresponding records from tblRemainingHours.

I used the: DMax("[RmgDate]","tblRemainingHours&quot, but I just get the one job that has the most recent date in the RmgDate field instead of for each job.

There are a few other things going on here, but is the whole SQL statement:

SELECT tblRemainingHours.JobID, tblRemainingHours.RmgDate, [RmgProTrode]+[RmgProSteel]+[RmgProMoldBs]+[RmgHSMTrodes]+[RmgHSMSteel]+[RmgHSMMoldBs]+[RmgCNCTrodes]+[RmgCNCSteel]+[RmgCNCMoldBs]+[RmgWire]+[RmgCNCEDM]+[RmgCNCManualEDM]+[RmgManMach]+[RmgGrinding]+[RmgPolish]+[RmgFitting] AS RmgHours, Sum(tblHoursDetail.HoursDetail) AS Actual
FROM tblRemainingHours INNER JOIN tblHoursDetail ON tblRemainingHours.JobID = tblHoursDetail.JobID
WHERE (((tblHoursDetail.HoursDate)<=DMax(&quot;[RmgDate]&quot;,&quot;tblRemainingHours&quot))
GROUP BY tblRemainingHours.JobID, tblRemainingHours.RmgDate, [RmgProTrode]+[RmgProSteel]+[RmgProMoldBs]+[RmgHSMTrodes]+[RmgHSMSteel]+[RmgHSMMoldBs]+[RmgCNCTrodes]+[RmgCNCSteel]+[RmgCNCMoldBs]+[RmgWire]+[RmgCNCEDM]+[RmgCNCManualEDM]+[RmgManMach]+[RmgGrinding]+[RmgPolish]+[RmgFitting]
HAVING (((tblRemainingHours.RmgDate)=DMax(&quot;[RmgDate]&quot;,&quot;tblRemainingHours&quot));

Thanks in advance. Hopefully this is a simple task that I just don't know how to do.

 

[dynamictiger]

I haven't gone through your SQL statement, but does Remaining hours have a relationship to jobs or do jobs have a relationship to remaining hours?

Or is this correct structure at all.

I would think a job has a known time allocation. Then a job takes an amount of time. The amount of time - time allocation is the remaining hours or overrun hours, depending.

HTH

 

[karassik]

tblJobs is One and tblRemainingHours is the Many. I want to sort for each job and for each job, I want the most recent entry of its corresponding job in tblRemainingHours.

My initial thought was to use the:
HAVING (((tblRemainingHours.RmgDate)=DMax(&quot;[RmgDate]&quot;,&quot;tblRemainingHours&quot));

But maybe since I want every job and not just the Max date, I should use:
HAVING (((tblRemainingHours.JobID)=DMax(&quot;[RmgDate]&quot;,&quot;tblRemainingHours&quot));

This I think will sort each JobID for the most recent date.

This make logical sense. I have not had a chance to test it. I will Monday morning.

Thanks,

Nishan