Query to tell me if a piece of equipment is due for service

If you don't know SQL at all, it's best to break the problem into pieces.

1) display an asset with the current mileage (G)

Create a new query, select tables tblAssets and tblAssets_Hrs_Miles. Select the field AssetID and just those fields needed to recognise the asset (as few as possible). From the second table, take only End_Of_Day_M???. Display the total row, leave "Group By" for all but the last, where you select Max.

This should provide (G). Save it, for example as qtotAssets_Current.

2) get last services

Create a new query on tblService_Record. Choose only three fields: Asset_ID, Service_Type_ID, and HrsMiles. Again, group on the first two, and use Max on the last one.

This provides (E) and (F). You can call it qtotService_Last

3) get the maintenance schedule

If the combination Asset_ID & Service_Type_ID is unique, you can skip this part. If not, proceed exactly like in 2), but for that table.

4) bring it together

In a new query, display qtotAssets_Current, qtotService_Last (linked on Asset_ID), and tblMaintenance (or the query you built for it -- linked on both Asset_ID and Service_Type_ID).

You should now have one row for each asset and each service type, with the values (G)(E)(A) in one row and (G)(F)(B) in the next. All that's left is then to compare them in an expression or in a criteria.

It's not as complicated as it seems. The SQL performing that in one query would be and then some.

Good luck!
(°v°)

Thanks harfang, I'll try it out in the morning and let you know how it turns out.

harfang,
     As far as I can tell, this is working great.  below is a picture of how I set up the query.  The only issue I can find in testing is that if a machine has never serviced it doesn't display.  It seems that in the qry I set up to display the last time a piece of equipment was serviced I'd need to tell it to display a 0 for any assets that does not have a service record.  How can I do this.

Of course, I should have anticipated that.

You can reverse the order of the sub-queries. First link on Maintenance, then on Service. Then double-click the links (the lines themselves), and specify that you want to see "all maintenance records", even if there are no matching services. The line should become an arrow.

As for the table 'service type', link it only once (remove the unnecessary link), in the new layout from 'maintenance'. You might have to change all links to arrows, if you get a message about "ambiguous joins":

    assets --> maintenance --> service
                                         \--> service type

Good luck!
(°v°)

I'm not entirely sure I understand.  I went through and re-did the joins and set all properties on each to show every record, etc...  I'm still getting the same result though, which works fine except for equipment that has never been serviced. Wouldn't the two other queries need to be altered (qryLast Service & qryassetCurrentHrsMiles) so that the data concerning a piece of equipment with no service record is available to qryWhenPMisDue?  Or, I could be entirely off-base :)  Below is the qry after I redid the joins.

It's good, but the arrows are in the wrong direction! You should also have a double link between 'maintenance' and 'last service', on both 'asset id' and 'service type id'. Finally, it's slightly better to link 'maintenance' to 'service type', so that the field is available even if there has been no service of that type.

Again, my feeble attempt at illustrating the idea:

    assets --> maintenance ==> service
                                         \--> service type

(°v°)

I just finally got your illistration attempt, I was reading it like flow chart, I think i've got it now.  However, the query still doesn't present the correct info.  It actually gives less information now, I'm not sure why.

What this query should show you is this.

* Every asset, with their highest 'hours miles' (first two columns)
* All records of tblMaintenance (provided there is at least one 'hours miles' record)
* The highest 'service' of the same type (or null if there was none)
* The service type.

You will then filter out records based on the Criteria: row. The expression might be more restrictive than you think, especially if you refer to fields that can be Null. Does the returned records seem OK without criteria? If yes, can you show the criteria you are using? If not do the subqueries return the expected records?

(°v°)

Below I've attached a screen shot of the qry results both without criteria and with.  It looks like it's displaying exactly what you described above without the criteria, but with the criteria it's not showing everything due for service.  I put the criteria under the service type field, if I put it anywhere else I get a 'data type mismatch' error.  Below is the criteria I've been using.  thanks again for all of you help, I really do appreciate it  

IIf(([MaxOfEnd_of_Day_MI_Hrs]-[MaxOfHrsMiles])>[PM_at_Hrs_Mi],[Service_Type])

Here are the results with the criteria

It's coming along fine. The last problem is the criteria.

The reason it fails has to do with the way Null is treated. Null isn't 0, and ever expression involving Null becomes Null (this is different from the way blank cells are handled in Excel). On the second row, asset 23, PM1

IIf(([MaxOfEnd_of_Day_MI_Hrs]-[MaxOfHrsMiles])>[PM_at_Hrs_Mi],[Service_Type])
IIf((600-Null)>250,'PM Number 1')
IIf(Null>250,'PM Number 1')
IIf(Null,'PM Number 1')
Null

And null is treated as a failure. To convert a column which can be Null to Zero, use the function Nz(): Nz(MaxOfHrsMiles).

I would put the criteria below the field PM_at_Hrs_MI:

    >= MaxOfEnd_of_Day_MI_Hrs - Nz(MaxOfHrsMiles)

Good luck!
(°v°)

I tried this but it didn't work.  It acutally only ended up displaying one record, the 5th one down on the table I posted above (24,550, , 750, PM Number 3).  However in messing around with this I found that if I don't have any criteria whatsoever I'm still not getting exactly what I should.  Below is a snapshot of qryLastService.  It displays two service records for asset number 10, one for PM1 and one for PM2.  Both should be displayed in qryWhenPMisDue, however, only the record of PM2 displays.  I'm not sure why it's doing this.  I thought perhaps I should solve this problem first.

When I tried the criteria you mentioned above, I had to change it slightly because I kept getting a syntax error.  I changed it to:
>=[MaxOfEnd_of_Day_MI_Hrs]-Nz([MaxOfHrsMiles])

Again, thatnks for you help in helping me solve this problem.

Also, I'm not sure if you wanted me to keep the criteria I had been using under the service type field or not.  But, if so, when I have both criteria in place, I don't get any results.  

I change it to <= and it works perfectly!  Thank you so much, I really do appreciate your time and patience!!!  Now I need to create a report out of this.  Keep any eye out for the questions I'm sure I'll have about that.  :)