Help with query

[CustomCode]

Hi I have a query that takes current kilometers and compares it with the next dry service and wet service kilometers. The query is set so the vehicle does not show on the report till it is due for a service.

The query works the same way by date. If the current date is equal or greater then the Dry or Wet date the unit will show up on the report.

It runs for seperate divisions and checks the status of the unit. If the unit is sold it will not show up for a service or safety.

Here is the problem:
NextSerType is a column that is suppose to say either Wet, Dry, CVI Inspection, Reefer Service or combination if a CVI and service are due.

It does not update the NextSerType in the query or the database
Here is the code

SELECT [Assets].AssetID, Departments.DepartmentName, [Assets].NextDryDate, [Assets].NextWetDate, [Assets].NextSerType, [Assets].CurrentOdo, [Assets].NextDryOdo, [Assets].NextWetOdo, [Assets].NextReefer, [Assets].CVIDate, [Assets].StatusID
FROM [Assets] INNER JOIN Departments ON [Assets].DepartmentID = Departments.DepartmentID
WHERE (((Departments.DepartmentName)="4" Or (Departments.DepartmentName)="44" Or (Departments.DepartmentName)="45" Or (Departments.DepartmentName)="48") AND (([Assets].StatusID)=1 Or ([Assets].StatusID)=5 Or ([Assets].StatusID)=6 Or ([Assets].StatusID)=7 Or ([Assets].StatusID)=8 Or ([Assets].StatusID)=9 Or ([Assets].StatusID)=10 Or ([Assets].StatusID)=11 Or ([Assets].StatusID)=12 Or ([Assets].StatusID)=13 Or ([Assets].StatusID)=14 Or ([Assets].StatusID)=15 Or ([Assets].StatusID)=16 Or ([Assets].StatusID)=17 Or ([Assets].StatusID)=18 Or ([Assets].StatusID)=19 Or ([Assets].StatusID)=20 Or ([Assets].StatusID)=21 Or ([Assets].StatusID)=22 Or ([Assets].StatusID)=24 Or ([Assets].StatusID)=25 Or ([Assets].StatusID)=27 Or ([Assets].StatusID)=29 Or ([Assets].StatusID)=30 Or ([Assets].StatusID)=31))
ORDER BY [Assets].NextDryDate;

Thx in advance

 

[Pat Hartman]

Here's a version of your query that will be easier to read:

SELECT [Assets].AssetID, Departments.DepartmentName, [Assets].NextDryDate, [Assets].NextWetDate, [Assets].NextSerType, [Assets].CurrentOdo, [Assets].NextDryOdo, [Assets].NextWetOdo, [Assets].NextReefer, [Assets].CVIDate, [Assets].StatusID
FROM [Assets] INNER JOIN Departments ON [Assets].DepartmentID = Departments.DepartmentID
WHERE Departments.DepartmentName IN("4", "44", "45", "48") AND [Assets].StatusID) IN(1, 5, 6, 7, 8, 9, 10, 11, 12, 13, 14, 15, 16, 17, 18, 19, 20, 21, 22, 24, 25, 27. 29, 30, 31)
ORDER BY [Assets].NextDryDate;

This is a select query so it would never update a table and there is no calculation in it that would return any value but what is contained in the table for NextSerType.

 

[CustomCode]

When the user check the service. The form calculates the service due and stores it in the NextSerType. In some cases the unit may be due for a Wet, CVI, Diff Oil Change, Trans Oil change, Hydraulic Oil Change, Reefer Service, Tire Rotation or Warranty Inspection.

A unit may be due for many of the listed items.
Currently to update the type of service requires the user to go to each unit and check.

Because the database holds over a thousand units and the date may affect the type of service due. I would like to run a query that will change the type of service due.

If I wanted to make an update query...How would I put an if statement

if the current date > = [Assets].NextWetDate then NextSerType = "Wet"

And

If the current date >= [Assets].CVIDate then NextSerType = "Wet and CVI"

And

If [Assets].CurrentOdo >= [Assets].NextReefer then NextSerType = "Wet and CVI and Reefer Service"

 

[Pat Hartman]

Your table is not normalized. That is why you are having trouble with this. You should have only one service date and a code that says what that service is. Using three separate dates, it doesn't make sense to compare them individually to the current date. They could each be > the current date so which value should be placed in the Next service type? Don't you need to compare them to each other to see which is the newest date and then compare that one to the current date?

 

[CustomCode]

Different units have different spreads between the service dates. Some units will get an Oil change every 30 days because they idle alot, where other will be 60 days etc. I also have to save them because I received request for the last date on the specific services. If I had a sample how to put multiple if statements into a update query, I would be able to figure it out for myself. I already started this one that partly works.

UPDATE Service SET NextSerType = NextSerType &" Safety"
WHERE CVIDate <=Now();


thx

 

[Pat Hartman]

You need three queries, not 1. The criteria for each update is separate and will produce a separate set of records. I still don't think you'll get the correct answer though.