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

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

safe.store.file@gmail.com - 21 Mar 2007 18:58 GMT

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?

Reply to this Message

John W. Vinson - 21 Mar 2007 19:42 GMT

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

Reply to this Message

safe.store.file@gmail.com - 21 Mar 2007 20:05 GMT

On Mar 21, 9:42 pm, John W. Vinson
<jvinson@STOP_SPAM.WysardOfInfo.com> wrote:

> >In a access database query I want to compare a field in record with
> >same field in preceding record
[quoted text clipped - 9 lines]
>
> 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

Reply to this Message

John W. Vinson - 21 Mar 2007 22:09 GMT

>Thank you very much
>You are right
[quoted text clipped - 10 lines]
>
>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]

Reply to this Message