MPG query

[tdimmick@escambia.k1]

I have a table that stores vehicle number, product, odometer, quantity and trantime as well as several other fields. All odometer readings are stored in the same field. I need to find the average miles per gallon over a 4 year period. Below is my query:

SELECT MainTrans.SITEID, MainTrans.USERID, MainTrans.VEHICLEID, MainTrans.ODOMETER, MainTrans.PRODUCT, MainTrans.QUANTITY, MainTrans.TRANTIME, ((SELECT A.ODOMETER FROM MainTrans AS A WHERE A.VEHICLEID = MainTrans.VEHICLEID AND A.TRANTIME IN (SELECT Max(B.TRANTIME) FROM MainTrans As B WHERE B.TRANTIME < MainTrANS.TRANTIME AND B.VEHICLEID = MainTrans.VEHICLEID))) AS PrevODOMETER, IIf(IsNull([PrevODOMETER]),Null,([ODOMETER]-Nz([PrevODOMETER]))/[QUANTITY]) AS MPG, MainTrans.TOTALCOST
FROM MainTrans
WHERE (((MainTrans.VEHICLEID) Like "*-*") AND ((MainTrans.ODOMETER) Not Like "0"))
ORDER BY MainTrans.VEHICLEID, MainTrans.TRANTIME;

When it finally does run I get the message:"At most one record can be returned by this subquery". I have tried upsizing the table to the server and running the query but then I get the message "IS NULL Statement only requires 2 arguments". Can anyone help?

 

[David R]

http://allenbrowne.com/ranking.html may be instructive.

 

[plog]

Can you post sample starting data from your table, then what data the query should produce based on that sample data? Include table and field names.

 

[tdimmick@escambia.k1]

Vehicle# Odometer Product TranDate Qty Dept
00010-08 63595 2 05-Oct-12 32.78 4108A
00030-08 64998 2 05-Oct-12 25.7 4108A
00030-08 65082 2 08-Oct-12 13.1 4108A
00024-08 67647 2 08-Oct-12 29.6 4108A
00023-08 73904 2 05-Oct-12 41.6 4108A
00020-08 81908 2 05-Oct-12 32 4108A
00022-08 86066 2 08-Oct-12 32.3 4108A
00020-05 91484 2 05-Oct-12 24.1 4108A
00012-07 92280 2 08-Oct-12 27.2 4108A
00022-07 93344 2 05-Oct-12 25 4108A
0002-095 97255 2 05-Oct-12 12.1 4108A
0002-095 97350 2 05-Oct-12 27.3 4108A

Since there are multiple odometer entries for each vehicle the query should group the vehicles together and sort by date then take the oldest odometer reading and subtract from the next odometer reading and so on to get the avg mpg. Once I get this part running I think I can figure out how to extract the product and vehicle type by year. This has me stumped.

 

[tdimmick@escambia.k1]

Oops, I did not give the correct field names. The table name is MainTrans; field names are VEHICLEID, ODOMETER, PRODUCT, TRANTIME, QUANTITY, AGENCY.
Sorry....

 

[plog]

halfway there:

... and then what data the query should produce based on that sample data?

 

[tdimmick@escambia.k1]

The query should produce the avg mpg for the fleet for a specific period of time.

 

[plog]

The operative word in my request was 'data'. More explanations are not data.

Please provide what data the query should return based on the sample data you already posted.

 

[tdimmick@escambia.k1]

The sample I posted previously was not a good sampling so I have posted a better data sample. The field names are first in the same order as the data. The query should create 2 fields "PREVODOMETER" AND "MPG"

VEHICLEID,ODOMETER,PRODUCT,TRANTIME,QUANTITY,AGENCY,
PREVODOMETER,MPG
00003-05 42369 2 19-Oct-07 20.3 4108A
00003-05 42482 2 22-Oct-07 19 4108A 42369 5.95
00003-05 42587 2 22-Oct-07 18.8 4108A 42482 5.59
00003-05 42682 2 23-Oct-07 15.3 4108A 42587 6.21
00003-06 32566 2 07-Mar-08 74.7 4108A
00003-06 33031 1 18-Mar-08 77.91 4108A 32566 5.97
00003-92 192275 3 15-Nov-04 2.8 4108A
00003-92 192376 2 17-Nov-04 17.3 4108A 192275 5.84

When I run the query it works and provides the information requested, but then immediately gives the error and all of the field values disappear.

 

[plog]

Now we come to explanation time. One question:

1. Is AGENCY relevant to making matches between records? Or are record matches made solely on VEHICLEID?

 

[plog]

Assuming matches are only made using VEHICLEID, then this SQL will get you PREVODOMETER and MPG:

SELECT MainTrans.VEHICLEID, MainTrans.ODOMETER, MainTrans.QUANTITY, DMax("[ODOMETER]","MainTrans","[VEHICLEID]='" & [VEHICLEID] & "' AND [ODOMETER]<" & [ODOMETER])*1 AS PREVODOMETER, ([ODOMETER]-DMax("[ODOMETER]","MainTrans","[VEHICLEID]='" & [VEHICLEID] & "' AND [ODOMETER]<" & [ODOMETER])*1)/[QUANTITY] AS MPG
FROM MainTrans
ORDER BY MainTrans.VEHICLEID, MainTrans.ODOMETER;

 

[tdimmick@escambia.k1]

Thank You very much, that worked!