Complicated SQL...

Discussion created by techtronix on Apr 17, 2018
Latest reply on Apr 18, 2018 by okramis

I have a data puzzle and I am looking for some direction...

One of my header tables stores inspection report data like (name, address, inspection date, inspection type, etc). Another table stores all the contact info and attributes for an establishment (estab name, inspection frequency (days)), city, etc.) 


I need to assemble a data collection that lists establishments that are "Due" for inspection based on the number of days past since the last inspection (the inspection frequency field holds the number of days for each establishment like "365"). It needs to only pull the most recent "full" inspection type for each place and ignore all older inspections that will build up over time.


I created a possible solution to this problem with a "log" file, but if a user deletes an inspection report, the log is not updated to reflect the current data and revert back to an older inspection date. Doesn't seem like a good design...


I have done this in other platforms but I had to create temporary tables and cursors to get all the data combined correctly. Can filemaker pull this off with execute SQL or some other method?