Mileage Query (Newbie) TIA

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

Thanks in advanc

I am attempting to set up a fleet database

One of the issues that I am having is that the workers sign the vehicle out in the morning, when they return at the end of the day they are suppost to enter in the Date, Mileage(at the end of the day), and which car they used

What I need to be able to do in my query is to have the date go back to the last day that mileage was entered and subtract the previous days mileage (last day used) from the next date to give actual milage driven on that particular day. I have tried date -1 however the problem is that the cars don't get signed out every day so when it runs the query it finds a "0" value on many of the days which screws things up. Anyone know of a database example that one could download which might help me along

The way that my data is structured in the table is as follows

Vehicle Date Mileag
1234 02/17/04 10,10
1234 02/16/04 10,00
1234 02/10/04 9,984
 
Try typing this into a fresh column of the Field row in your query:


PriorMileage: (SELECT TOP 1 Dupe.Mileage FROM MyTable AS Dupe WHERE
(Dupe.Vehicle = MyTable.Vehicle) AND (Dupe.Date < MyTable.Date) ORDER BY
Dupe.Date, Dupe.MyID )

Replace "MyTable" with the name of your table, and "MyID" with the name of
your primary key field.

The results are read-only, so suitable for a report.

If that is not satisfactory, see:

http://support.microsoft.com/default.aspx?scid=kb;en-us;208953&Product=acc2000

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

Bill said:
Thanks in advance

I am attempting to set up a fleet database.

One of the issues that I am having is that the workers sign the vehicle
Click to expand...
out in the morning, when they return at the end of the day they are suppost
to enter in the Date, Mileage(at the end of the day), and which car they
used.
What I need to be able to do in my query is to have the date go back to
Click to expand...
the last day that mileage was entered and subtract the previous days mileage
(last day used) from the next date to give actual milage driven on that
particular day. I have tried date -1 however the problem is that the cars
don't get signed out every day so when it runs the query it finds a "0"
value on many of the days which screws things up. Anyone know of a database
example that one could download which might help me along?
 
Allen,
I like your use of "Dupe" as the table name alias. It is much more
descriptive then my usually "A" or whatever. I gonna steal and use this.
 
You're welcome.

Anything that aids self-documenting readability without circumlocutory
verbosity, eh? :-)
 
Back
Top