Prevenatative Maintenance

[Annette]

I have a table of assets, a table of asset services, a table of service types that note services types that are of PM class. I would like to determine which assets have NOT done a PM service type.

I can query by service type which service is a PM service and I can look at an assets service to determine when was the LAST time the service occurred. What I need help in is determining which assets have not had a service type.

For example, oil changes are a PM service type. I look at the asset services table and can find the last time that service was performed and for which asset, what I can't determine is what asset has not had that service performed.

Any suggestions on how to create a query to do that?

 

[Access Developer]

Create a query joining the Assets table with the Query determining Last time
a PM service was performed -- records returned without any "last time a PM
service was performed" info (e.g., the unique id of the asset from that
input query or you could make a null unique asset id in the last PM query a
condition and receive only records showing the asset of interest).

Larry Linson
Microsoft Office Access MVP
Co-Author, Microsoft Access Small Business Solutions, Wiley 2010

 

[Annette]

Here is the issue. What if there is a PM that has never been done for an asset? Let's say car AAA has never had an oil change. The query that shows the last time it was done would not have a record. Would I have to include inthe query that shows the last time it was done the PM's that were never done? How do I do that?

 

[Bob Quintal]

Here is the issue. What if there is a PM that has never been done
for an asset? Let's say car AAA has never had an oil change. The
query that shows the last time it was done would not have a
record. Would I have to include in the query that shows the last
time it was done the PM's that were never done? How do I do that?

What you need is a query that shows all assets and all service types,
You then create a second query using the first query and the asset
services table. Change the join type to a "Left Join" from the asset
query to the services table, then filter on the servivce type and null
service date in the service table

 

[Access Developer]

Isn't that what you asked in the first place? Here's a quote from your first
post in this thread:

I would like to determine which assets have NOT done a PM service type.

In what I proposed, it is the *absence* of data about the service in the
query result for each asset that indicates the service has never been
performed on the asset.

--
Larry Linson
Microsoft Office Access MVP
Co-Author, Microsoft Access Small Business Solutions, Wiley 2010

Here is the issue. What if there is a PM that has never been done for an
asset? Let's say car AAA has never had an oil change. The query that shows
the last time it was done would not have a record. Would I have to include
in the query that shows the last time it was done the PM's that were never
done? How do I do that?