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

Article translations Article translations
Article ID: 208953 - View products that this article applies to.
This article was previously published under Q208953
Advanced: Requires expert coding, interoperability, and multiuser skills.

This article applies to a Microsoft Access database (.mdb) and to a Microsoft Access project (.adp).

Expand all | Collapse all

SUMMARY

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 that 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. The example is divided into two sections: one for an Access database, and the other for an Access project.

In a Microsoft Access database (MDB)

  1. 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
    					
  2. View the table in Datasheet view, and enter the following records in the table:
       Date        Mileage   Gallons
       -----------------------------
       7/08/1999   12340     14.8
       7/13/1999   12700     12.6
       7/18/1999   13090     13.7
       7/25/1999   13425     11.9
    					
  3. Create a new, blank query based on the MileageRecord table. Add the Date and Mileage fields to the query grid.
  4. Click the Properties button on the toolbar to view the property sheet, and then select the title bar of the MileageRecord table. Set the Alias property of the table to Mile1.
  5. 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.
  6. 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.
  7. Enter the following expression in the fifth column in the query grid:
       MPG: ([Mileage] - [PrevMileage]) / Gallons
    						
    This field calculates the miles per gallon.
  8. Run the query.

In a Microsoft Access project (ADP)

  1. Create the following new table, and then save it as MileageRecord:
       Table: MileageRecord
       --------------------
       Column Name: ID
        Datatype: uniqueidentifier
        Allow Nulls: no
        Default Value: newid()
       Column Name: Date
        Datatype: datetime
       Column Name: Mileage
        Datatype: decimal
        Length: 9
        Precision: 18
        Scale: 2
       Column Name: Gallons
        Datatype: decimal
        Length: 9
        Precision: 18
        Scale: 2
    					
  2. View the table in Datasheet view, and enter the following records in the table:
       Date        Mileage   Gallons
       -----------------------------
       7/08/1999   12340     14.8
       7/13/1999   12700     12.6
       7/18/1999   13090     13.7
       7/25/1999   13425     11.9
    					
  3. Create the following stored procedure:
    CREATE PROCEDURE "Calculate_Mileage_Proc"
    AS
    SELECT 
      MileageRecord.Date,
      MileageRecord.Mileage,
      (SELECT MAX(mileage) 
        FROM mileagerecord 
        WHERE mileagerecord.mileage < mileagerecord1.mileage) 
        AS PrevMileage,
      MileageRecord.Mileage - (SELECT MAX(mileage)
        FROM mileagerecord
        WHERE mileagerecord.mileage < mileagerecord1.mileage) 
        AS Elapsed, 
      (MileageRecord.Mileage - (SELECT MAX(mileage)
        FROM mileagerecord
        WHERE mileagerecord.mileage < mileagerecord1.mileage))
        / MileageRecord.Gallons 
        AS MPG
    
    FROM MileageRecord 
    
    INNER JOIN
     MileageRecord MileageRecord1 ON 
     MileageRecord.id = MileageRecord1.id
    					
  4. Save and run the stored procedure.

Results

Note that you receive the following results from the query or the stored procedure:
   Date      Mileage   PrevMileage   Elapsed   MPG
   ------------------------------------------------------------
   7/08/99   12340
   7/13/99   12700     12340         360       28.5714285714286
   7/18/99   13090     12700         390       28.4671532846715
   7/25/99   13425     13090         335       28.1512605042017
				

REFERENCES

For more information about subqueries, click Microsoft Access Help on the Help menu, type SQL subqueries in the Office Assistant or the Answer Wizard, and then click Search to view the topic.

Properties

Article ID: 208953 - Last Review: June 29, 2004 - Revision: 2.0
APPLIES TO
  • Microsoft Access 2000 Standard Edition
Keywords: 
kbhowto KB208953

Give Feedback

 

Contact us for more help

Contact us for more help
Connect with Answer Desk for expert help.
Get more support from smallbusiness.support.microsoft.com