Trying to extract the last inspection date as well as those that have never occurred.

[Macawac1]

Hello all,

Help please!! As in the title I am trying to extract a list of required equipment inspections- to show the last date the equipment was inspected, as well as those pieces of equipment that have never had an inspection. The frequency of checks are based upon the equipment category (ie vehicle or lifting table) and the check type (ie user check or service or MOT etc.)

I can get a query to show me all checks that need to be done.

I can get a query to show me the last check that has been carried out on each piece of equipment.

What I cannot seem to manage is to combine these.

Here is my structure (which I am now starting to doubt is correct):




Thank you in advance for any help or advice

[isladogs]

I've seen this database structure before & remember that I suggested a number of changes to simplify it
Is this a duplicate of a thread at Access World Forums or a different thread
Either way, could you please post the link to the previous thread

[Macawac1]

Apologies for any confusion. You may be referring to the training one that I asked a similar question of a few weeks ago, however I have tried to replicate what I did with that one with this one but still could not figure it out.

I am not a member of any other Access forums so have not posted same question anywhere else.

[isladogs]

OK but you definitely posted here previously.
It appears you didn't make any of the suggested changes to the structure
e.g. removal of tblRoles ; add Role field to tblEmployees

Please can you provide the link so I can see what was said before

[June7]

OP has 5 threads. You might just look at the one(s) you have a post in.

[isladogs]

I know I can ...
My point was that I believe others might find it useful to see the previous thread(s).
Its still relevant if only for the previous comments about structure
https://www.accessforums.net/showthr...672#post382672
https://www.accessforums.net/showthr...613#post380613

[Macawac1]

Oh - I am so sorry - I posted the wrong picture!! I was very very tired last night no wonder I caused confusion. You are absolutely correct Ridders - this is the old picture of the training DB - and yes - I did make the suggested changes which helped me no end - this is the original image and not the completed one.

This new question relates to a different database regarding equipment maintenance. CORRECT image shown below:



Again - apologies for the confusion.

Regards.

[Macawac1]

Apologies again - this is the CORRECT image shown below: (I can't seem to edit my posts?)

Again - apologies for the confusion.

Regards.

[isladogs]

Apologies again - this is the CORRECT image shown below: (I can't seem to edit my posts?)
Again - apologies for the confusion.

Regards.

Thanks - that explains it .....
Now what was the question again ?

OK - more seriously, please post the queries you have so far so we can see what you've tried.
Thanks

[Macawac1]

Hi again,

Think I have cracked it!

I have used three queries to achieve the results I wanted to see which are to show All equipment checks that are required according to equipment category and inspection type, along with the LAST inspection date for those that have been carried out - those not carried out to show a null value.

Here is how I have done it, however I am not sure if this is the best way to achieve it or whether I have approached it in a clumsy manner! Grateful for any input

To obtain dates of last inspection:

SELECT tblEquipment.EquipmentID, tblEquipment.CategoryID, tblInspectionRecord.InspectionTypeID, Max(tblInspectionRecord.InspectionDate) AS MaxOfInspectionDate, tblEquipment.DateWithdrawn
FROM tblEquipment INNER JOIN tblInspectionRecord ON tblEquipment.EquipmentID = tblInspectionRecord.EquipmentID
GROUP BY tblEquipment.EquipmentID, tblEquipment.CategoryID, tblInspectionRecord.InspectionTypeID, tblEquipment.DateWithdrawn
HAVING (((tblEquipment.DateWithdrawn) Is Null));

To obtain list of required checks:

SELECT tblInspectionSchedule.CategoryID, tblInspectionSchedule.InspectionTypeID, tblEquipment.EquipmentID
FROM (tblCategory INNER JOIN tblEquipment ON tblCategory.CategoryID = tblEquipment.CategoryID) INNER JOIN tblInspectionSchedule ON tblCategory.CategoryID = tblInspectionSchedule.CategoryID;

To bring the two together:

SELECT qryEquipmentInspectionList.CategoryID, qryEquipmentInspectionList.InspectionTypeID, qryEquipmentInspectionList.EquipmentID, qryLastInspection.MaxOfInspectionDate
FROM qryEquipmentInspectionList LEFT JOIN qryLastInspection ON (qryEquipmentInspectionList.EquipmentID = qryLastInspection.EquipmentID) AND (qryEquipmentInspectionList.InspectionTypeID = qryLastInspection.InspectionTypeID) AND (qryEquipmentInspectionList.CategoryID = qryLastInspection.CategoryID);




As said above, I am not sure if this is the best way to do this so any advice or guidance will be very gratefully received

Kind regards,
Karen.