One to Many Query Question

[karassik]

Thanks all who read and answer my and everyone elses questions.

I am not quite sure how to go about this. I have a main table: tblJobs and JobID relates to many records in tblRemainingHours. I want to get the most recent remaining hours date (RmgDate) record in my query.

I have tried using max and last, both of which don't work.

I have even used =Dmax("[RmgDate]","tblRemainingHours","[JobID]= " & [JobID]) this gives me a "JobID refers to multiple JobID....."


Everything I try gives me all the records of each job in tblJobs. Someone has definately had this problem..

Thanks,

Nishan

 

[IFELSETHEN]

Hi,
This is where a Index Key would be nice. A number that incurments for each item in your tblRemainingHours table. Then you could find the last date using a MAX(Index Key). But you prob don't have a key like that, then you can ORDER by using an DEC. For decurment sort (Going from the Last to the First).
John

 

[karassik]

I do have an index key, I think...the Primary key for tblRemainingHours? I'll try that, but how does one sort by DEC? Ascending? Then how would I get the last record.

I'll try the max of my index. Many thanks.

Nishan

 

[IFELSETHEN]

Sorry .... ASC for the sort. John

 

[karassik]

Maybe I misunderstood how to do this John, but I am still getting all the records on the many side (in tblRemainingHours for each JobID). I have = Max([RemainingHoursID]) in the RemainingHoursID, or SQL: HAVING (((tblRemainingHours.RemainingHoursID)=Max([RemainingHoursID]))

if I instead use :SELECT Max(tblRemainingHours.RemainingHoursID) AS MaxOfRemainingHoursID, then I still get many records in tblRemainingHours for each JobID, but not all.

Any ideas?

 

[IFELSETHEN]

Yep ..... use the TOP statment and just get the TOP one record. .....John

 

[karassik]

The TOP Statement gives me the most recent one record of the query, where I want the top value for each JobID not just the most recent one. I can't believe I can't figure this out.

Thanks.

 

[IFELSETHEN]

Try grouping and then the TOP. John

 

[karassik]

I figured it out and you set me in the right direction. I needed to use a subquery in my index field.

HAVING (((tblRemainingHours.RemainingHoursID) In (SELECT Max(tblRemainingHours.[RemainingHoursID]) AS [MaxOfRemainingHoursID] FROM tblRemainingHours GROUP BY tblRemainingHours.[JobID])


This is not an easy problem. You would have thought that there would be an easier way since this issue would seem to come up often.

Thanks for all your responses John.

Nishan