Autonumber in query

  • Thread starter Thread starter Mike
  • Start date Start date
M

Mike

Hello,

How can I add an record ID to query results? The records
are not necessarily entered in order so I cannot number
them then.

Thanks
Mike
 
Mike

Unless you are adding the query results to a table, your "recordID" would be
different every time you run the query (after making data changes). What
are your intentions...? <G>

What is it that having a recordID on your query results will allow you to
do...?
 
These are for beginning and ending odometer readings for
trucks. I need to make sure that they flow, the one end
is the next begin. I have the query sorting them by
beginning odometer so if the number I can dlookup for any
gaps.

Thanks for looking.
Mike
 
Mike

So, you want to ensure that the ending odometer reading from the most recent
use is the same as the beginning odometer reading of the next use... no
matter whether there's been one day or a long weekend or a
month-in-the-shop?

Do you have one field ([OdometerReading]) or two ([OdometerStart],
[OdometerEnd])? Do you have [DateTimeStart] and [DateTimeEnd] fields, in
case the truck is out on an overnight run?

How "close" is "good enough"? If I drive on Monday and record an ending
reading of 12345 (after rounding), and you drive today and show a start
reading of 12346 (you and I round differently), what should happen? What
happens if the "yard crew" takes the truck down the street to the gas
station and refills it?

There may be a few more details to work out first... <g>
 
Jeff,

Those gas station runs are what I am trying to discover.
I have two fields [Beg Odom] and [End Odom]. I need to
find these "gap miles" to fuel tax reporting. As for
rounding, there is a procedure in place to truncate the
tenths by the driver.
Many of the trucks are out overnight. Along with gas
runs, sometimes the driver takes the truck home for the
night. It's my job to find those gap miles and record
them.
So what I want to do is order the odom readings by unit#
and then compare the current end to the next beginning
reading. If they are not the same, I need to view those
records for review of gas runs or entry error by my
department.

Again, I really appreciate your looking at this and I hope
I am not too much bother :)

Mike
 
Mike

One approach would be to create a procedure that steps through a sorted
recordset, saving the "previous" EndOdom value and comparing it to the
subsequent BeginOdom value. This approach would require some coding
experience.

One of the other 'group readers may be able to offer an 'all-in-SQL'
approach.
 
Back
Top