ACC: How to Compare a Field to a Field in a Prior Record

ID: Q120273

The information in this article applies to:

Microsoft Access versions 2.0, 7.0, 97

SUMMARY

Advanced: Requires expert coding, interoperability, and multiuser skills.

This article shows you how to use a subquery to compare the values in a record with the values in another record. In order to use the method described in this article, the table you are using must have one or more fields that can be compared by using the GREATER THAN (>) or LESS THAN (<) operator.

MORE INFORMATION

The following example demonstrates how to calculate miles per gallon by subtracting a recorded mileage from the current mileage, and then dividing by the number of gallons of gasoline recorded in the current record:

Create the following new table, and then save it as MileageRecord. Do not create a primary key for the table:


      Table: MileageRecord
      --------------------
      Field Name: Date
      Data Type: Date/Time
      Field Name: Mileage
      Data Type: Number
      Field Size: Single
      Field Name: Gallons
      Date Type: Number
      Field Size: Double

View the table in Datasheet view, and enter the following records in the table:


       Date      Mileage   Gallons
       ---------------------------
       7/08/94   12340     14.8
       7/13/94   12700     12.6
       7/18/94   13090     13.7
       7/25/94   13425     11.9

Create a new, blank query based on the MileageRecord table. Add the Date and Mileage fields to the query grid.

Click the Properties button on the toolbar to view the property sheet, and then select the title bar of the MileageRecord table. Set the table's Alias property to Mile1.

Enter the following expression in the third column in the query grid.

NOTE: In the following sample expression, an underscore (_) at the end of a line is used as a line-continuation character. Remove the underscore from the end of the line when re-creating this expression.
```
      PrevMileage: (Select Max(Mileage) from MileageRecord _
      Where Mileage < Mile1.[Mileage]) 
```
This expression is a subquery that finds the highest mileage in the MileageRecord table that is less than the mileage of the current record.

Enter the following expression in the fourth column in the query grid:
```
      Elapsed: [Mileage] - [PrevMileage] 
```
This field calculates the difference between the current and previous mileage entries.

Enter the following expression in the fifth column in the query grid:
```
      MPG: ([Mileage] - [PrevMileage]) / Gallons 
```
This field calculates the miles per gallon.

Run the query. Note that you receive the following results:


      Date      Mileage   PrevMileage   Elapsed   MPG
      ------------------------------------------------------------
      7/08/94   12340
      7/13/94   12700     12340         360       28.5714285714286
      7/18/94   13090     12700         390       28.4671532846715
      7/25/94   13425     13090         335       28.1512605042017

REFERENCES

For more information about subqueries, search the Help Index for "subqueries," or ask the Microsoft Access 97 Office Assistant.

Additional query words: differential calculus

Keywords : QrySqlvw
Version : 2.0 7.0 97
Platform : WINDOWS
Issue type : kbhowto