Question Can anyone help with my Sub Query mayhem?

[Chelle1661]

RUNNING DIFFERENCE PROBLEM:
I am an amateur Access user and trying to get a running difference into a query based on a simple vehicle table and a vehicle readings table. This query for a report which will show the daily difference in the odometer readings. The table lists multiple vehicles in a fleet and a reading every day for each of these vehicles. I need to be specific about the previous reading i.e. pull through the previous day's reading for that specific vehicle, not the multiple values [of the fleet] for that particular day. I have been troubleshooting this and trying to find a solution on line [and I may now have a total jumble in my select query]! Please help if you can as I keep getting syntax or operator error messages. Below is what I have in my SQL view showing my attempts so far - as you can see I have not referenced the VehicleID adequately [which will ensure that the running difference pulls through the one relevant value], and I am not sure where to place the sub query in the main query:

SELECT Vehicles.VehicleSortOrder, Vehicles.Vehicle, VehicleReadings.VehicleReadingDate, Vehicles.VehicleCategoryID, VehicleReadings.VehicleReading, VehicleReadings.VehicleLitres, VehicleReadings.OperationsDayID, Vehicles.VehicleID, VehicleReadings.VehicleReadingID, Vehicles.VehicleNumber, Vehicles.VehicleOperationTypeID, Vehicles.OpsAreaID, Vehicles.VehicleUpdateDate
(RunningDifference: SELECT VehicleReadingID, VehicleReadings.VehicleReading, VehicleReading - nz((SELECT VehicleReading from VehicleReadings WHERE VehicleReadingDate =
(SELECT max(VehicleReadingDate) FROM VehicleReadings where VehicleReadingDate < T.VehicleReadingDate))) AS RunningDiff FROM VehicleReadings as T order by VehicleReadingDate)
FROM Vehicles RIGHT JOIN VehicleReadings ON Vehicles.VehicleID = VehicleReadings.VehicleID;

 

[RainLover]

You will need to adjust to suit your needs.

Hope it helps.

Attached Access 2003 sample.

 

[Chelle1661]

Thanks, will give it a try!

 

[Chelle1661]

Hi Rainlover,

I have tried using your example query but am still getting error messages re: syntax or operators! Very frustrating as I am copying your expressions exactly apart from my field and table names and it therefore should run as your example query runs!

 

[Chelle1661]

Re: Can anyone help with my Sub Query mayhem? Running Difference Problem

Well I managed to get a reference to the previous reading with help from Allen Brown re: sub queries.

http://allenbrowne.com/subquery-01.html

I now have PriorValue and Difference fields in my query so that I can base calculations on them. However, when trying to bring these fields into my report I get a message: Multi-Level GROUP BY clause is not allowed in a sub query..
Can anyone advise?
Ever onward..!

 

[Chelle1661]

Re: Can anyone help with my Sub Query mayhem? REFERENCE PREVIOUS RECORD

As per above, I have not been able to resolve the problem with Multi-Level GROUP BY clause [which is not allowed in a sub query] therefore I cannot total on the report that references the query.

Now I must seriously consider DLookUp to find the previous records' value in the query so that I can base the difference on that rather than a sub query.

As I have to look up the one record in a field [VehicleReading] relating to the mileage reading for the previous day [VehicleReadingDate] and reference that specific vehicle [VehicleID] in a table holding data for numerous vehicles on numerous dates, I need to get the syntax and operators right for this to work.

Please help if you can - I am trying to find answers on line in the meantime..