Re: [MSAccessProfessionals] Calculate overnight electricity consumption

It looks like readings decrease through the day. Does this work for you? I changed column names to remove spaces and made up a table name.

SELECT ElectricityConsumption.ActivityDate, ElectricityConsumption.Activity, ElectricityConsumption.Residents,

 ElectricityConsumption.StartTime, ElectricityConsumption.FinishTime, ElectricityConsumption.InitialReading,

 ElectricityConsumption.FinalReading, ElectricityConsumption.Consumption,

(SELECT FinalReading FROM ElectricityConsumption C

  WHERE ElectricityConsumption.ActivityDate = C.ActivityDate +1)-[InitialReading] AS OverNight

FROM ElectricityConsumption;

Duane

---

From: MSAccessProfessionals@groups.io <MSAccessProfessionals@groups.io> on behalf of asanteza@gmail.com <asanteza@gmail.com>
Sent: Friday, October 25, 2024 11:33 PM
To: MSAccessProfessionals@groups.io <MSAccessProfessionals@groups.io>
Subject: [MSAccessProfessionals] Calculate overnight electricity consumption

 

Hi Everyone,

I am trying to keep a database of my prepaid electricity consumption in MS Access 2019 version. As the table shows, I am able to calculate the daily usage from the initial and final readings of my meter. However, I am struggling to figure out the formula for Overnight usage (Final reading of previous day minus Initial reading of current day).

 

Activity date	Activity	Residents	Start time	Finish time	Initial reading	Final reading	Consumption	Overnight
21/10/2024	Normal + laundry	2	06:00	20:00	348.20	329.42	18.78
22/10/2024	Normal + cook	2	06:00	20:00	322.87	307.28	15.59
23/10/2024	Normal + laundry	2	06:00	20:00	300.04	284.04	16.00
24/10/2024	Normal + cook + pump	2	06:00	20:00	274.00	255.30	18.70
25/10/2024	Normal	2	06:00	20:00	249.38	234.38	15.00