In a access database query I want to compare a field in record with
same field in preceding record

Can that be done in access query?
How?

On 21 Mar 2007 10:58:14 -0700, (E-Mail Removed) wrote:

>In a access database query I want to compare a field in record with
>same field in preceding record
>
>Can that be done in access query?

Not as stated, because there's no such thing as "the preceding record" in any
useful sense. An Access Table is an unordered "heap" of records. You must have
some field in the table which gives a precedence order. You can use a
Subquery, a Self Join, or a DLookup to find the value from the "previous"
record. If you could post some information about the structure of your table
someone should be able to help.

John W. Vinson [MVP]

On Mar 21, 9:42 pm, John W. Vinson
<jvinson@STOP_SPAM.WysardOfInfo.com> wrote:
> On 21 Mar 2007 10:58:14 -0700, safe.store.f...@gmail.com wrote:
>
> >In a access database query I want to compare a field in record with
> >same field in preceding record
>
> >Can that be done in access query?
>
> Not as stated, because there's no such thing as "the preceding record" in any
> useful sense. An Access Table is an unordered "heap" of records. You must have
> some field in the table which gives a precedence order. You can use a
> Subquery, a Self Join, or a DLookup to find the value from the "previous"
> record. If you could post some information about the structure of your table
> someone should be able to help.
>
> John W. Vinson [MVP]

Thank you very much
You are right
The database is for student learning advance the table structure is :
date,name ,grade

I have query that get the data of a single student with fields :
date,name,grade ,advance (calculated field)

The problem is with the calculated field (advance) which should
compare the grade of the student with his(or her) preceding grade to
describe the state of the student either advancing or need some
helping procedure

I hope that information is describing my problem properly

On 21 Mar 2007 12:05:52 -0700, (E-Mail Removed) wrote:

>Thank you very much
>You are right
>The database is for student learning advance the table structure is :
>date,name ,grade
>
>I have query that get the data of a single student with fields :
>date,name,grade ,advance (calculated field)
>
>The problem is with the calculated field (advance) which should
>compare the grade of the student with his(or her) preceding grade to
>describe the state of the student either advancing or need some
>helping procedure
>
>I hope that information is describing my problem properly

Ok... try adding to your query a calculated field to look up the most recent
previous grade for this student:

Advance: DLookUp("[grade]", "[tablename]", "[Name] = """ & [Name] & """ AND
[Date] = #" & DMax("[Date]", "[tablename]", "[Name] = """ & [Name] & """ AND
[Date] < #" & [Date] & "#"))

This won't be as efficient as a subquery but should be ok for a small
database.

STRONG suggestions:

Name and Date are both reserved words and should NOT be used as fieldnames.

I'd suggest in any case using a unique student ID (names are NOT unique,
someday you might have two students both named Bill Smith).

It's better to store names in FirstName and Surname fields (so you can search
or sort by either one).

John W. Vinson [MVP]