Query Criteria to fill in a new field.

G

G. Weiler

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.
 
F

fredg

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.
 
Top