Calculation between records

[Victic31]

Hi,
A few months ago I asked a question on this forum and some very nice people
responded (Thank you). Unfortunately I now can't find the thread on the forum
and have to ask the question again (It. would be a really useful feature if
there was a way of just listing or filtering the thread(s) by user id).
Anyway here goes...

I have a database that is keep track of vehicle maintenance and servicing etc.
I have reports that show the maintenance history of the vehicles, however, I
want to be able to show the elapsed mileage between each entry.
For example (in simple terms)...
REP_DATE DESCRIP MILEAGE ELAPSED
01/01/09 Service 10000
01/02/09 Service 20000 10000
01/03/09 Service 28000 8000
01/04/09 Test 32000 4000

I know there is probably a very simple solution to this, but it currently
escapes me.
Your help is appreciated (especially if your one of those who replied the
first time).

Cheers
Victor

 

[KARL DEWEY]

Try this --
SELECT Victic31.REP_DATE, Victic31.DESCRIP, Victic31.MILEAGE, (SELECT TOP 1
[Victic31].[MILEAGE] - [XX].[MILEAGE] FROM [Victic31] AS [XX] WHERE
[XX].[REP_DATE] < [Victic31].[REP_DATE] ORDER BY [XX].[REP_DATE] DESC ) AS
Elapsed
FROM Victic31;

 

[Victic31]

Many thanks for the help. After I got my head around Alias's etc it all began
to make some sort of sense.
However, now I have been able to translate this into my database I now have
another problem. (Posted seperately as "Calc field causing data loss in
report")

Thanks again,
regards
Victor

Try this --
SELECT Victic31.REP_DATE, Victic31.DESCRIP, Victic31.MILEAGE, (SELECT TOP 1
[Victic31].[MILEAGE] - [XX].[MILEAGE] FROM [Victic31] AS [XX] WHERE
[XX].[REP_DATE] < [Victic31].[REP_DATE] ORDER BY [XX].[REP_DATE] DESC ) AS
Elapsed
FROM Victic31;

--
Build a little, test a little.

Hi,
A few months ago I asked a question on this forum and some very nice people
responded (Thank you). Unfortunately I now can't find the thread on the forum
and have to ask the question again (It. would be a really useful feature if
there was a way of just listing or filtering the thread(s) by user id).
Anyway here goes...

I have a database that is keep track of vehicle maintenance and servicing etc.
I have reports that show the maintenance history of the vehicles, however, I
want to be able to show the elapsed mileage between each entry.
For example (in simple terms)...
REP_DATE DESCRIP MILEAGE ELAPSED
01/01/09 Service 10000
01/02/09 Service 20000 10000
01/03/09 Service 28000 8000
01/04/09 Test 32000 4000

I know there is probably a very simple solution to this, but it currently
escapes me.
Your help is appreciated (especially if your one of those who replied the
first time).

Cheers
Victor