Query Criteria to fill in a new field.

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

Given fields: [CustomerID], [MeterReading], [ReadDate].

Anyone know how to fill in a new field, getting data from last month’s Customer [MeterReading] to this month’s [PreviousMeter], thus being able to calculate [Usage]? Am presently using Excel to do this, but I would rather not copy & paste if I can get around it, keeping all actions in Access.
 
Given fields: [CustomerID], [MeterReading], [ReadDate].

Anyone know how to fill in a new field, getting data from last month¢s Customer [MeterReading] to this month¢s [PreviousMeter], thus being able to calculate [Usage]? Am presently using Excel to do this, but I would rather not copy & paste if I can get around it, keeping all actions in Access.
Click to expand...

Here is an example using automobiles and their mileage driven.
Change the field and table names as needed.
SELECT tblMultiCars.Auto, tblMultiCars.dtedate, tblMultiCars.Mileage,
Abs([Mileage]-DLookUp("Nz([Mileage])","tblMultiCars","[Auto] = '" &
[Auto] & "' and Month([dtedate]) = " & Month([dteDate])+1)) AS
MilesTraveled
FROM tblMultiCars
ORDER BY tblMultiCars.Auto, tblMultiCars.dtedate;

Try this.
SELECT tblMeters.CustomerID, tblMeters.ReadDate,
tblMeters.MeterReading,
Abs([MeterReading]-DLookUp("Nz([MeterReading])","tblMeters","[CustomerID]
=" & [CustomerID] &" and Month([ReadDate]) = " & Month([ReadDate])+1))
AS Usage
FROM tbMeters
ORDER BY tblMeters.CustomerID, tblMeters.ReadDate;

Change the tblMeters to your actual table name.
The above assumes CustomerID is a Number datatype, and the meters are
read once each month.
 
Back
Top