Returning most recent record from left joined query 1

[Gerbers404]

Hello all,

I've been searching this forum for awhile, and have found people with similar problems, but haven't been able to find any resolution to this. I have a query on a left-joined table, and I want to only return the recrods with most recent (max) Calibration Date from the right table(Calibration_Header). The 4 fields from
the right table I'm using in the query have different values in them for any given Gage_ID, so when I try to use the Max function on the date field, it still shows the other records with differences from the other 3 fields in the right table. Wow, did that make sense or what? Anyway, here's the sql statement:

SELECT [Gage Master].GM_Type, [Gage Master].Gage_ID, [Gage Master].Status, [Gage Master].Gage_SN, [Gage Master].Asset_No, [Gage Master].Model_No, [Gage Master].Current_Location, [Gage Master].Calibration_Frequency, [Gage Master].Calibration_Frequency_UOM, [Gage Master].Notes, [Gage Master].Last_Calibration_Date, [Gage Master].Next_Due_Date, [Gage Master].Operating_Range, [Gage Master].Plus_Tolerance, [Gage Master].Minus_Tolerance, Calibration_Header.CertNo, Calibration_Header.Results, Calibration_Header.CalibType, Max(Calibration_Header.Calibration_Date) AS MaxOfCalibration_Date
FROM [Gage Master] LEFT JOIN Calibration_Header ON [Gage Master].Gage_ID = Calibration_Header.Gage_ID
GROUP BY [Gage Master].GM_Type, [Gage Master].Gage_ID, [Gage Master].Status, [Gage Master].Gage_SN, [Gage Master].Asset_No, [Gage Master].Model_No, [Gage Master].Current_Location, [Gage Master].Calibration_Frequency, [Gage Master].Calibration_Frequency_UOM, [Gage Master].Notes, [Gage Master].Last_Calibration_Date, [Gage Master].Next_Due_Date, [Gage Master].Operating_Range, [Gage Master].Plus_Tolerance, [Gage Master].Minus_Tolerance, Calibration_Header.CertNo, Calibration_Header.Results, Calibration_Header.CalibType, Calibration_Header.Calibration_Date
ORDER BY [Gage Master].Gage_ID, [Gage Master].Status;

I figure that if I didn't have to group by the other 3 fields from the Calibration_Header table, it would work fine, But have had no luck getting anything to work.

Any ideas and suggestions would be appreciated. Thank you!

Gerbers404

 

[mpastore]

You will have to add in additional fields, I only included key ones:


SELECT [Gage Master].gm_type, [Gage Master].gage_id, [Gage Master].status, h.certno, h.results, h.calibtype, h.calibration_date
FROM [Gage Master], Calibration_Header AS h
WHERE [Gage Master].gage_id = h.gage_id
and h.calibration_date in (select max(calibration_date) from calibration_header where gage_id = h.gage_id)
ORDER BY [Gage Master].gage_id, [Gage Master].status;


This assumes that there is not a duplicate calibration date in calibration_header for any given gage_id.

I would first run my query and analyze results before adding in additional fields.

Mike Pastore

Hats off to (Roy) Harper

 

[Gerbers404]

Mike,

Thanks for your reply! It works exactly the way that I want for those records with a calibration date, however, there are records in the Gage Master table that do not have calibration dates that I would also like to be able to view. There are 500 records in the Gage Master table and only 350 results are displayed with this query (Those with Cal dates) Is there any way to do this in this query? That is why I had a Left Join in my attempt, but I think I was off the mark. Thank again, Mike!

Gerbers404

 

[mpastore]

I think this will do what you need:

SELECT [Gage Master].gm_type, [Gage Master].gage_id, [Gage Master].status, h.certno, h.results, h.calibtype, h.calibration_date
FROM [Gage Master] LEFT JOIN Calibration_Header AS h ON [Gage Master].gage_id = h.gage_id
WHERE (((h.calibration_date) In (select max(calibration_date) from calibration_header where gage_id = h.gage_id)) or h.gage_id is null)
ORDER BY [Gage Master].gage_id, [Gage Master].status;



Mike Pastore

Hats off to (Roy) Harper

 

[Gerbers404]

Mike,

Thank you very much! Works perfectly now! I appreciate it.

Gerbers404