Can I calculate the percent of a record compared to its predecessor?

S

safe.store.file

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?
 
J

John W. Vinson

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?
Click to expand...

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]
 
S

safe.store.file

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]
Click to expand...

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
 
J

John W. Vinson

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
Click to expand...

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]
 
Top