tdimmick@escambia.k1
New member
- Local time
- Today, 02:26
- Joined
- Nov 13, 2013
- Messages
- 6
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?
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?
