This article was previously published under Q208953
This article has been archived. It is offered "as is" and will no longer be updated.
Advanced: Requires expert coding, interoperability, and multiuser skills.
This article applies to a Microsoft Access database (.mdb) and to a Microsoft Access project (.adp).
This article shows you how to use a subquery to compare the values in arecord with the values in another record. In order to use the methoddescribed 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.
The following example demonstrates how to calculate miles per gallon bysubtracting a recorded mileage from the current mileage, and then dividingby 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)
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:
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 Alias property of the table 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.
In a Microsoft Access project (ADP)
Create the following new table, and then save it as MileageRecord:
CREATE PROCEDURE "Calculate_Mileage_Proc"ASSELECT 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 MPGFROM MileageRecord INNER JOIN MileageRecord MileageRecord1 ON MileageRecord.id = MileageRecord1.id
Save and run the stored procedure.
Note that you receive the following results from the query or the stored procedure: