Find difference of records in different fields in the same table.

[Steve]

I use a database to keep track of my mileage for IRS reasons. I am able to
find my total business mileage but I also need to find my personal mileage on
a daily, monthly, and yearly basis. Basicly anytime I want. Date range?

This is how my table is set up that I enter my mileage data into.

Trip Date Starting Location Ending Location Start Mileage End
Mileage
8/21/2008 St Louis Home 1000
1100
8/22/2008 Home Pevely 1120
1300

What I need to do is find the difference between the Start Mileage of the
8/22 record and the End Mileage of the 8/21 record. This would give me the
personal mileage that I am looking for.

PS. I also have a qry already that spits out business mileage which is just
the difference between the End Mileage and the Start Mileage for each record.

Any help will be a great help. I am new to Access. Thanks.

 

[Lord Kelvan]

are you trying to find the information for the next date for each set
of records so if you had

21/8/2008 1000 1100
22/8/2008 1120 1300
24/8/2008 1350 1500

you want it to display as the results of a query

21/8/2008 1000 1100
22/8/2008 1120 1300 20
24/8/2008 1350 1500 50

the milage will always increase i take it and will never be less than
the one before

Regards
Kelvan

 

[Lord Kelvan]

anyways i did a couple of queries for the above post of mine

SELECT mileage.[Trip Date], mileage.[Starting Location], mileage.
[Ending Location], mileage.[Start Mileage], mileage.[End Mileage],
(select count([trip date])+1 from mileage as submileage2 where mileage.
[trip date] > submileage2.[trip date]) AS recordnum
FROM mileage;

save that as qrymileagesetup

SELECT qrymileagesetup.[Trip Date], qrymileagesetup.[Starting
Location], qrymileagesetup.[Ending Location], qrymileagesetup.[Start
Mileage], qrymileagesetup.[End Mileage], (select qrymileagesetup.
[start mileage]-submileage.[end mileage] from qrymileagesetup as
submileage where qrymileagesetup.recordnum=submileage.recordnum+1) AS
Expr1
FROM qrymileagesetup;

and save the above as whatever and run it

AS A NOTE
you need to change the word mileage for the table in the from
statements in the first query to whatever your table is named.

to use these queries create a new query cancel the pop up window then
whild in design view click on view then sql view then paste the above
into seperate queries saving the first as instructed

hope this helps

Regards
Kelvan