Hi Steve,
Basically it sounds like all you want is to find the
greatest Hobbsin that is less than the Hobbsin of your
current record. You could probably do this by using a
subquery or the DMax() function.
To use the DMax function, your calculated field would
have the expression:
DMax("[Hobbsin]","YourTableName","[Hobbsin] < " &
[Hobbsin])
This basically says to go to the specified table
(YourTableName in the example), create a domain of
records consisting of all records where the value in the
[Hobbsin] field of the table is less than the value of the
[Hobbsin] field of your query, and return the maximum
value found within that domain in the [Hobbsin] field.
Of course, you would have to adjust the table name and
field name to match your database. Also, the last
Hobbsin is referring to the query field, so if you have
changed the field title in the query that one should
match the query while the other two would match the table
field name.
You could also do the same thing by using a subquery for
the calculated field to do basically the same thing, but
I'm sure that I would butcher the sql if I were to write
an example. I'm not sure which method would be faster, I
would suspect maybe the subquery. If the Dmax() function
seems too slow, you may want to do a new post to ask for
help with the subquery syntax.
Hope that helps.
-Ted
-----Original Message-----
Thanks Ted:
I was afraid of that. There really isn't an
identification field in the table.
Maybe you can tell me how to bring the data from the one
record to the next. ie record 1 Hobbsout = 5678.9
Hobbsin = 5679.3. New (next) record has hobbsout = to
Hobbsin of the previous record carried forward.
There is an knowledge base article "210236" about "Fill
Record with Data from Previous Record Automatically"
however, it does not quite work. I would do Hobbsout to
Hobbsout, but not Hobbsout to Hobbsin.
Thanks again for your help.
Steve
-----Original Message-----
Hi Steve,
When you say you want to compare to the previous record,
is there some type of record identification field in your
table? If so, I would say that you could use the Dlookup
() function and construct a sql string. But, if not, it
may be a little trickier.
I haven't used a records recordnumber from the table in
query calculations before. Hopefully, someone else could
post to say if that is possible.
-Ted
-----Original Message-----
I currently have a table that has the following fields.
From,To,Hobbsout,Hobbsin.
I want to get to the point that I only have to enter data
once. Therefore, I want to eliminate the Hobbsout and
replace the Hobbsin with just Hobbsreading because the
Hobbsout is always the same as Hobbsin of the previous
record so why record it. (stay with me, this can't be to
hard).
My problem now becomes the querie. How do I create a
calculated field in a querie that looks at the previous
record and calculates the time between the readings. i.e.
=([Hobbsreading in record 33] minus [Hobbsreading in
record 34])
Any and all suggestions will help me go from a zero to a
hero.
Thanks
.
.
.
