Querying Multiple Tables- Aggregate Function Error

[nickdawes28]

Hello,

I am trying to figure out how to query data in my database based on a number of different criteria.

I have reached a stage where I can get all the data I need from one query, however I can't figure out how to further query this data to return records from a table with the most recent date only. I have searched the forum, googled and experimented myself but I am running into "Aggregate Function" errors. Hope somebody can help!

In this scenario there are 3 tables. tblJobs, tblEquipment and tblInspectionLog. Each tblJobs record can have multiple tblEquipment records attached to it, and each tblEquipment record can have multiple tblInspectionLog records attached to them.

I would like to query the database for what tblEquipment records have been assigned to a tblJob ID and also return only the tblInspectionLog record with the latest Inspection_Date field.

At the moment I am able to see tblEquipment records attached to tblJobs, however duplicate records appear due to multiple InspectionLog records associated with the equipment.

I have tried to filter records from tblInspectionLog using the "Max" criteria under Inspection_Date field in my query. This however returns an "Aggregate Function" error.

Happy to provide further details for anybody interested.

Thanks,
Nick

 

[Galaxiom]

Use a Max on the date field in an aggregate query on the InspectionLogs and return only the key and Max date.

Then join these results to the other fields you need to show.

 

[nickdawes28]

Hi Galaxiom,

Could you lay that out for me in noob terms? I am not familiar with aggregate functions...

My table names are tblEquipment, tblInspectionLog and the field names used are Inspection_ID, Inspection_Date and Equipment_ID

Have tried a few variations of code I have found through google but unable to get this to work.

Thanks

 

[namliam]

Something along these lines:

Select equipment_id, max(inspectiondate)
from tblEquipment as E join tblInspectionlog as IL on E.Equipment_id = IL.Equipment_ID
Group by equipment_id

 

[nickdawes28]

Ok, thanks guys managed to make this work