cumulative total on multiple records

[foshizzle]

I recently posted a question regarding this query, but in going back, I need a different kind of total.

I am looking for a way to get a progressive cumulative total from daily entries on Odometer records from multiple vehicles. (My current SQL query is not working) I will eventually run a report from these between two dates. I found the thread here, but am unsure of how to implement this - if this is indeed what I need in lieu of my current code..?

http://www.access-programmers.co.uk/forums/showthread.php?t=158467

My current problem is that, not all my previous entries are correct; Im not sure what is happening to the numbers about halfway through...

For clarification, here is my current code and an example of what is needed. The issue is highlighted in yellow. Excel shows the correct calculation

SELECT qry_ODO_Table.ID AS OdomAlias, qry_ODO_Table.ODate, qry_ODO_Table.VehicleNum, Nz(DFirst("Odometer","qry_ODO_Table"),0) AS StartOD, Nz(DLast("Odometer","qry_ODO_Table","[ID] < " & [OdomAlias]),0) AS Previous, qry_ODO_Table.Odometer, [Odometer]-[Previous] AS Difference
FROM qry_ODO_Table
ORDER BY qry_ODO_Table.ID;

 

[plog]

DFirst and DLast should be removed, not just from your code but from Access as built in functions.

You should get the Previous odometer reading by using the Dmax function (http://www.techonthenet.com/access/functions/domain/dmax.php). It takes the same arguments as Dfirst/Dlast (field name, data source name, criteria). To make it work, in the criteria section you have to set it so that it looks at records where the odomoter reading is less than the current record. That ensures you pull the odometer reading that is just below the current record.

 

[foshizzle]

I'm a little unclear on how to use it to "get the previous odometer reading" and make it get the "odometer reading is less than the current record" in the same statement, if that's what you mean. To me, it sounds like it should be two separate statements?

Previous: DMax("Odometer","tblOdometer"," [Odometer] < [Odometer]")

 

[plog]

Dmax pulls the maximum value in a field (Odometer) in a datasource (tblOdometer) that meets your criteria (<[Odometer], which refers to the current record's odometer value). So if you pull the highest value just under the current value that means you are getting the odometer reading that is just prior to the current one.

Now, the confusing part is realizing that [Odometer] inside quote marks refers to a different field than when [Odometer] is outside quote marks. This is what your DMax should look like:

Previous: DMax("Odometer","tblOdometer"," "[Odometer] < " & [Odometer])

Notice that in the criteria, one [Odometer] is inside the quote marks and one is outside of them. The one outside of them refers to the current query you are running. Whatever value is in that record will be used in the Dmax. The [Odometer] inside the quote marks will refer to the one inside the table you specified--in this case tblOdometer.

 

[foshizzle]

Something is still amiss

Previous: DMax("Odometer","tblOdometer","[Odometer] < " & [Odometer])

SELECT qry_ODO_Table.ODate, qry_ODO_Table.VehicleNum, Nz(DFirst("Odometer","qry_ODO_Table"),0) AS StartOD, qry_ODO_Table.Odometer, DMax("Odometer","tblOdometer","[Odometer] < " & [Odometer]) AS Previous, [Odometer]-[Previous] AS Difference
FROM qry_ODO_Table;

 

[plog]

You probably need to include VehicleNumber in your criteria.

 

[foshizzle]

I'm lost

Previous: DMax("Odometer","tblOdometer","[Odometer] < " & [Odometer] AND "[VehicleNum]"= [VehicleNum])

 

[plog]

No your not, you're basically there, you just need to understand how strings work. The criteria argument of Dmax is a string. Strings can be made up of actual text, which must be inside quote marks; and also of the value inside variables, which exist outside of quote marks.

Like I tried to explain earlier, when you used [Odometer] you were using it in two ways--one as the name of a field inside your table which meant it was text and had to be inside quote marks and you were also using [Odometer] as a variable to retrieve the actual value inside it which meant for that [Odometer] had to be outside quote marks.

Suppose the variable [Odometer] has a value of 2, then that means this:

"[Odometer]=" & [Odometer]

will evaulate to this string:

"[Odometer]=2"

The [Odometer] outside of the quote marks inserts its value into the string. Now you just need to do the same for VehicleNum. This should be the criteria:

"[Odometer] < " & [Odometer] & " AND [VehicleNum]=" & [VehicleNum]

You are using two text parts ("[Odometer]<", " AND [VehicleNum]=") and two variables ([Odometer], [VehicleNum]). The text parts remain as they are and the variables get their values substituted into them. The ampersands (&) just concatenate all the pieces together.

 

[foshizzle]

Oh wow. Ok, I had no idea. I thought these were different ways of referencing a control or value depending on it's location, like form or subform, etc... (Still trying to get the hang of this.) Ive been working on this same project for almost 6 months now..

So, if I read you right,
"[Odometer]=" & [Odometer] will give me a string of "[Odometer]=OdometerValue"
and say I have vehiclnum 185, 186 and 189 (which I do)
"[VehicleNum]=" & [VehicleNum] will give me a string of "[VehicleNum]=185" or "[VehicleNum]=186" or "[VehicleNum]=189"

So DMAX and this will give the values depending on "[Odometer] < maxvalue from each record" AND supposedly where "[VehicleNum]=VehicleNum"

This column is coming up blank now.

Previous: DMax("Odometer","qry_ODO_Table","[Odometer] < " & [Odometer] & " AND [VehicleNum]"=[VehicleNum])

Thanks for your continued help

 

[plog]

The equal sign in the criteria needs to be inside the quote marks and you need an ampersand before the variable:

....& "[VehicleNum]=" & [VehicleNum]

 

[foshizzle]

Im sorry. I dont know why I am having so much trouble. I still cannot get it working. I looked up some info on DMAX and found this site http://msdn.microsoft.com/en-us/library/office/ff835050(v=office.15).aspx

So I looked back and the data type for VehicleNum is short text. so to try and get it working with the symtax you gave, iI added single quotes around VehicleNum as well.

Previous: DMax("Odometer","qry_ODO_Table","[Odometer] < " & [Odometer] & " AND " & "[VehicleNum]= ' & [VehicleNum]'")

 

[foshizzle]

Hi,
Just an update. Still playing with it. I have the string data displaying for Previous now using

Previous: DMax("Odometer","qry_ODO_Table","[Odometer] < " & [Odometer] & " AND " & "[VehicleNum]=" & "[VehicleNum]")

The bad news is, the numbers are off again. Please see attachment.
Thanks for your patience in this.

Full SQL

SELECT qry_ODO_Table.ODate, qry_ODO_Table.VehicleNum, qry_ODO_Table.Odometer, DMax("Odometer","qry_ODO_Table","[Odometer] < " & [Odometer] & " AND " & "[VehicleNum]=" & "[VehicleNum]") AS Previous, [Odometer]-[Previous] AS Difference
FROM qry_ODO_Table;

 

[foshizzle]

I kept looking and got it to work using another query.
I used the following sites as a reference and built a 3rd query for my calculations.
http://allenbrowne.com/subquery-01.html#AnotherRecord
http://www.access-programmers.co.uk/forums/showthread.php?t=228931

Thanks for your help and guidance