substract values in more than one record

  • Thread starter Thread starter Sandeman
  • Start date Start date
S

Sandeman

Perhaps it is very simple but i can't find it. I have this table
tblKilometers.

CarID Month Amount of kilometers on teller(at the
end of the month)
AABBCC 1 1000
AABBCC 2 2000
AABBCC 3 2500
... .... ... ...

Nu zoek ik de sql code die automatische de laatste kilometerstand aftrekt
van de voorlaatste. Dus eigenlijk de verreden kilometers per maand berekend
zoals hieronder weergegeven.
I am searching for some SQL code to substract the the last amount of
kilometers on the teller and the one before. As a result i have the
kilometers driven in a month like in the next table.


CarID month kilometers driven
AABBCC 1 1000
AABBCC 2 1000
AABBCC 3 500

Can anybody help me with this code?
 
There was a recent post, I believe in this group, on the subject of finding
the difference between two rows -- it may have been in relation to "meter
reading". If your newsgroup reader doesn't provide a way to do a
search/find, try checking at Google.com on this topic.

You might also trying looking for the responder, Tom Ellison, as he
regularly offers elegant SQL solutions (and may have been the responder on
the above topic).

Good luck

Jeff Boyce
<Access MVP>
 
Dear Sandeman:

With Jeff's introduction, maybe I'd better say something:

SELECT CarlID, Month, TellerReading
TellerReading - Nz(SELECT TellerReading FROM YourTable T1
WHERE T1.CarlID = T.CarlID AND T1.Month = T.Month - 1), 0)
AS DistanceDriven

Here's a simple solution. A better solution would find the largest
Month value less than the current Month value, especially important in
the case that a month was skipped (assuming you'd want it to find the
month before that one.) However, this takes a 2 level, nested
subquery correlated on the outer query's Month number, which Access
doesn't like. This makes for a more difficult solution, so I'll pass
on that unless you find you would need that one.

Do your "tellers" always start at 0 at the beginning of month 1 (or
whatever month the Carl starts in).

Please let me know if this helped.

Tom Ellison
Microsoft Access MVP
Ellison Enterprises - Your One Stop IT Experts
 
"Out" is the functional keyword.

Tom

Thanks, Tom (I'd hoped you were 'out there').

Jeff
Click to expand...

Tom Ellison
Microsoft Access MVP
Ellison Enterprises - Your One Stop IT Experts
 
Back
Top