calculation using values in previous record

[109bow]

I don't know if this is possible, I'm hoping someone can help!
I have a query which lists train wheel diameters and I need to calculate the wheel wear per kilometer, to predict the life of a particular wheel.
Attached is a portion of the query put into Excel, hopefully this explains what I'm trying to achieve.
The formula I'm trying to use is along the lines of:
wheel wear= ([ave pre wd]-[ave post wd from previous record where axle location is the same])/([km]-[km from previous record where axle location is the same]).
Any help would be very much appreciated!!!!

 

[Uncle Gizmo]

I suspect the method(s) on Rogers Access Library - Running Sum HERE:-

http://www.rogersaccesslibrary.com/...l?SID=1ad20d6294bfb7bf5d764e964fca75638888889

just might be adapted to your problem...

In any event, running sum, running gear, running trains is too coincidental not to mention....

 

[109bow]

Thanks Uncle Gizmo, unfortunately I haven't made any progress with the link you provided. I am thinking about tackling it another way. By creating another that Dsums the [ave pre wd] and [ave post wd] by group and then do the calculations. Im not sure it will work but I cant see another way.
So far I have;
SELECT wheelwearcalc.[Axle Location], wheelwearcalc.Date, wheelwearcalc.KM, wheelwearcalc.kmnow, wheelwearcalc.[ave pre wd], wheelwearcalc.[ave post wd], Format(DSum("[ave pre wd]","[wheelwearcalc]","[Axle Location] = " & [Axle Location] & " [Axle Location] <=" & [Axle Location]),"""Text""") AS RunningSum
FROM wheelwearcalc;
but this returns an error in the Runningsum.
I help in sorting this would be much appreciated, as I think I have reached my knowledge levels with Access

 

[plog]

You are going to need a subquery to identify the prior record for each record. This will be done by identifying the prior date of that record. This will be that SQL:

SELECT wheelwearrcalc.*, DMAX("[Date]", "wheelwearrcalc", "[Axle Location]='" & [Axle Location] & "' AND [Date]<#" & [Date] & "#") AS PriorDate  
FROM wheelwearcalc

Paste that into a query (you might have to fix the date comparison on the DMax--I never get that right) and save that query. Then you create a new query with it and wheelwearcalc. You join the 2 data sources on Axle Location and then PriorDate to Date, that will make all the data you need available in that query.

 

[109bow]

plog, many thanks for your input. Can you please tell me what fields I am to include in the new query, as I am totally in the dark!
Thanks

 

[plog]

What do you mean "new query". I gave you the SQL for the subquery, now you are to write the main one. In the main one, include all the fields you want/need.

 

[109bow]

Sorry, I'm not familiar with subqueries. Will have a go making it work with your code

 

[109bow]

plog, to be honest, I'm not sure where to go with this. I have put your code into the main query:
SELECT [09ts data].[Axle Location], [09ts data].Date, [09ts data].KM, kmnow2.kmnow, [09ts data].[ave pre wd], [09ts data].[ave post wd]
FROM [09ts data] INNER JOIN kmnow2 ON [09ts data].[short no] = kmnow2.[short no]
WHERE ((([09ts data].KM)>1000))
ORDER BY [09ts data].[Axle Location], [09ts data].Date DESC;
(SELECT wheelwearcalc.*, DMAX("[Date]", "wheelwearcalc", "[Axle Location]='" & [Axle Location] & "' AND [Date]<#" & [Date] & "#") AS PriorDate
FROM wheelwearcalc);
When I run it, it produces an error. SQL is bit of a mystery to me so any help getting this to work would be very grateful

 

[plog]

You paste my code into a brand new query--in SQL view. You save that query with its own name. Then you build a new query using it and wheelwearcalc.

 

[109bow]

plog, I have followed your instructions, pasted your code into a new query, wheelwearcalc1, which works fine. I have then created another query, wheelwearcalc2, and linked the 2 fields. however in design view when I drag any field from either query into the design grid and run the query I get "Query is too complex", code for wheelwearcalc2 is:
SELECT
FROM wheelwearcalc1 INNER JOIN wheelwearcalc ON (wheelwearcalc1.[Axle Location] = wheelwearcalc.[Axle Location]) AND (wheelwearcalc1.PriorDate = wheelwearcalc.Date);
How do I get the required fields into the new query without an error coming up?
Many Thanks!

 

[plog]

You can manually type them in the SELECT clause:

SELECT wheelwearcalc.[Axle Location], wheelwearcalc.[Date]
FROM...


Can you upload a sample of your database? That query shouldn't be giving Access any issues.

 

[June7]

Here is another method that use TOP N criteria in the subquery. http://allenbrowne.com/subquery-01.html#AnotherRecord

 

[109bow]

Hi, I have changed the tables so that they are now saved as normal tables not linked and have assigned an id to each record in both tables, in the hope it might speed things up. However wheelwearcalc is extremely slow to load.
Any guidance on how I can achieve the calculation I describes earlier would be very much appreciated.
Many thanks, Ben

 

[plog]

By its nature that's just a very slow query--it takes a lot to find a prior record. The more records in wheelwearcalc, the worse its going to be.

 

[109bow]

so you would expect more than 5000 records to take quite a long time then

 

[The_Doc_Man]

I'll digress to tell you why you were having problems.

You wanted the value from the previous record. Like the Matrix, "There IS no previous record." Tables are not stored neat and pretty.

There are a couple of ways to determine what you mean by "previous record" but the whole concept isn't easy. You see, Access doesn't "officially" care about table order. It has an index for things that have a primary key, but order of storage has nothing to do with key values - at least, nothing formal. This is because Access was implemented using set theory. When you do something to a table, the implementation makes it seem as though everything happened at once. (OK, we know it really DIDN'T all happen at once - but the code, to be compliant with set theory, has to make it look that way.)

When you talk about "previous record" you have to qualify it... "Previous" with respect to what method of ordering? If you time-tagged the records when you entered them, then a query ordering by timetag is ONE way to provide an order. If you have a sequence number (autonumber), a query ordering the records by that number would also give you an order. If you open a table, the odds are that it will be in some unrecognizable order.

Therefore, you need the subquery to impose order on the records so that you can find the one that is "previous" based on your criteria.

 

[June7]

Advise not to use spaces or punctuation/special characters (underscore only exception) in names, nor use reserved words as names. Date is a reserved word.

Do not have to repeat the alias field names on every line of UNION query. The first SELECT line defines the field names. There is a limit of 50 SELECT lines.

SELECT [00/01/1900] As [ReadDate], '02' As UnitNumber, [02 x 01] As [KM] FROM [Accu KM] UNION ALL
SELECT [00/01/1900], '04', [04 x 03] FROM [Accu KM] UNION ALL
SELECT [00/01/1900], '06', [06 x 05] FROM [Accu KM] UNION ALL
...

Trying to understand your JOIN queries. Table units has fields [unit] and [short no]. In query kmnow2 you join units to kmnow1 by [short no] = [UnitNumber]. However, you pull [unit] into the query and rename it [short no], then that alias field is joined to [short no] in 09data. Somewhat confusing but figured it out and determined kmnow2 query is not necessary. The following query is equivalent to wheelwearcalc.

SELECT kmnow1.Date, kmnow1.UnitNumber, kmnow1.KM AS kmnow, units.unit AS shortNo, [09data].id, [09data].KM, [09data].[Axle Location], [09data].Date, [09data].[ave pre wd], [09data].[ave post wd]
FROM 09data INNER JOIN (units INNER JOIN kmnow1 ON units.[short no] = kmnow1.UnitNumber) ON [09data].[short no] = units.unit
WHERE (((kmnow1.Date)=Date()))
ORDER BY units.unit, [09data].[Axle Location], [09data].Date DESC;

The following query pulls the previous KM from the UNION query.

SELECT kmnow1.Date, kmnow1.UnitNumber, kmnow1.KM,
(SELECT TOP 1 Dupe.KM FROM kmnow1 AS Dupe WHERE Dupe.UnitNumber = kmnow1.UnitNumber AND Dupe.Date < kmnow1.Date ORDER BY Dupe.UnitNumber, Dupe.Date DESC) AS PriorKM
FROM kmnow1;

Then I renamed the UNION query and created a new table from the UNION query and named it kmnow1. The nested query opens much faster but still takes forever to move to last record. Since I can't remember where BREAK is on this HP laptop, I finally killed the app.

 

[109bow]

Thanks to everyone for taking the time to look into this and replying. I feel it may take me some time to get to grips with this puzzle.
June7, you are totally correct about using spaces and reserved words, this I am only finding out as I go along as I am still very new to Access
Many thanks again