counting Days between Services

[Seb]

Hi guys

bit of a newbie here....but could you pls help me on this one!!!
Í have a table that looks kinda like this:

SRVDate - Date/Time
Fleet_Num - Text
Odo - number
ServiceType - Text
ServicedBy - Text
DiffOils - YES/NO
Gearbox_Oils - YES/NO
ValveAdjust -YES/NO

I need to extact the fleet numbers, the day the last 5 "B" services were done and then calculate how many days between each "B" service....

Kinda like this:

Truck1 B Service 01/01/2006
Truck1 B Service 01/02/2006 31 Days since last service
Truck1 B Service 03/02/2006 2 Days Since last service

I know how to extract all the data but its the subtracting the dates that killing me!!!!
Any help guys?

 

[FoFa]

Use the DATEDIFF function

 

[Seb]

Hi FOFA
I do have a primary key in the table. its "ID"

 

[FoFa]

Um, what does DATEDIFF have to do with primary keys?
DATEDIFF gives the number of (days, weeks, months, years) differance between two dates.

 

[Seb]

sorry FOFA......someone else replied to me and asked if I had a primary key!!! I answered to you by mstake....

i'm pretty much going mad trying this out though....

fofa, if you could help me on this one????

I'm going to have to join from one table to the next and show the most recent 5 records per truck....so here's the idea I was going with but is not working and I'm not sure where the Datediff would it into this:

SELECT TOP 5 tblService.SrvDate, tblFleet.Fleet_Num, tblService.ServiceType, *
FROM tblFleet LEFT JOIN tblService ON tblFleet.Fleet_Num = tblService.Fleet_Num
WHERE (((tblService.ServiceType)="B"))
ORDER BY tblService.SrvDate Desc;

but this just gives me the 5 most recent tblService.srvDate records

Thanks for your help this far by the way

 

[stopher]

You need a way of looking up the previous record so that SRVDate from the previous record can be used in your current record. One way to do this is to use DMAX to find the maximum date from all records with a date earlier that the current date.

Here's an example sql (assuming a table name of services)...

SELECT Services.serviceID, Services.SRVDate, DateDiff("d",DMax("SRVDate","Services","SRVDate<#" & [SRVDate] & "#"),[SRVDate]) AS DaysSinceLast
FROM Services

I've attached the working example
Stopher

 

[Seb]

That is kinda what I was thinking but how would I do it for each fleet_num (ie. truck number?)
What I need to do is find out how many days or kilometres between "B" Services per fleet_num which is linked to tblFleet.fleet_num....

Not sure if you get my drift???

 

[stopher]

Seb
Sorry I gave you copletely the wrong attachment (to someone elses problem!).

Take a look at this attachment. Query1 shows the number of days between each service for ServiceType B for all vehicles for all services. You can limit the number of services displayed by using the TOP n statement.

Note that the query shows the PrevServDate field for each record. This is the key to using DateDiff here. I used a similar expression in the DateDiff calc.

Although I've counted days here, the principle to count kilometres will be much the same.

One final point, I've created a function to convert none US date formats to US date formats (I assume you are not in the US since you mention Kilometres). The reason for this is that Access assumes all dates are in US format when using its functions. This won't affect how they are displayed to the user but will ensure the datediff function and < operator perform correctly.

hth
Stopher

 

[Seb]

stopher....

thats exactly what I was looking for.
You mention that getting the Odometer reading would be the same idea but what would I use in stead of Datediff???
Is there something similar to Datediff but for numbers in stead of dates?

 

[stopher]

Hi

You can use minus (-) because you just want to know the difference between two numbers (you could probably have got away with using minus for the dates too but datediff is more appropriate). Obviously since minus is an operator rather than a function, the syntax is slightly different.

Anyway, here’s an example of how you can generate the Odo diff.

SELECT 
  Services.ServiceType,
  Services.Fleet_Num,
  Services.serviceID,
  DMax("SRVDate","Services","Fleet_Num='" & [Fleet_Num] & "' and ServiceType='" & [ServiceType] & "' and SRVDate<#" & USDate([SRVDate]) & "#") AS PrevServDate,
  Services.SRVDate, 
  DateDiff("d",DMax("SRVDate","Services","Fleet_Num='" & [Fleet_Num] & "' and ServiceType='" & [ServiceType] & "' and SRVDate<#" & USDate([SRVDate]) & "#"),USDate([SRVDate])) AS DaysSinceLast,  
  Int(DMax("Odo","Services","Fleet_Num='" & [Fleet_Num] & "' and ServiceType='" & [ServiceType] & "' and SRVDate<#" & USDate([SRVDate]) & "#")) AS PrevOdo, 
  Services.Odo, [Odo]-DMax("Odo","Services","Fleet_Num='" & [Fleet_Num] & "' and ServiceType='" & [ServiceType] & "' and SRVDate<#" & USDate([SRVDate]) & "#") AS OdoSinceLast
FROM Services
WHERE Services.ServiceType = "B"
ORDER BY Services.Fleet_Num, Services.SRVDate

There’s an assumption here that max odo will be the most recent service before the current one i.e. for each successive service, the odo will increase (not decrease!!). I think this is reasonable ?

Note you will need to add odo to my table if you are going to implement it there.

Hth
Stopher

 

[Seb]

you're a legend!

 

[Seb]

Hey Stopher

I have another question. What would I do if the ODO goes over 1000000?
And quite a few of their trucks go over that.....
The statement you helped me with selects the highest ODO reading as the PrevOdo and then it screws the whole thing up.....?
Any ideas?