Daily Water consumption calculation (1 Viewer)

[Kshano]

Hi everyone,

I am setting a Database to keep track of daily water usage of 8 homes, each have their own independent meter and I have already setup a database with all daily readings for each home. I just need to create a calculation on daily water usage for a monthly report , the way we calculate water usage is meter reading from "Today" - meter reading from "yesterday" give us "yesterday's" water consumption. But I have no idea how to calculate this. Will really appreciate your comments.

Cheers

 

[plog]

Without specifics of your database we can't give you a specific solution. So in general you must first get the prior date of each record. This can be done using DMax

MS Access: DMax Function

This MSAccess tutorial explains how to use the Access DMax function with syntax and examples. The Microsoft Access DMax function returns the maximum value from an Access table (or domain).

www.techonthenet.com

Build a query, let's call this query 'sub1', on your table and create a calculated field with the prior date for each record, let's call this field [PriorDate]:


PriorDate: DMax("[YourDateField]", "YourTableName", "[YourDateField]<#" & YourDateField & "#")


Then build a new query using your table and sub1, join them via [PriorDate] to the date record in your table. From there you can bring in both values needed in your calculation.

 

[MajP]

To calculate the daily useage you can use a subquery or a dlookup.

Table1 Table1

MeterReadingID	ReadingDate	MeterReading	BldgID_FK
1​	1/1/2021​	10​	1​
2​	1/2/2021​	25​	1​
3​	1/3/2021​	45​	1​
4​	1/4/2021​	80​	1​
5​	1/1/2021​	100​	2​
6​	1/2/2021​	200​	2​
7​	1/3/2021​	350​	2​
8​	1/4/2021​	475​	2​

SELECT table1.meterreadingid,
       table1.readingdate,
       table1.meterreading,
       table1.bldgid_fk,
       (SELECT Max(meterreading)
        FROM   table1 AS B
        WHERE  B.bldgid_fk = table1.bldgid_fk
               AND b.readingdate < table1.readingdate) AS YesterdayMeterReading,
       [meterreading] - [yesterdaymeterreading]        AS Useage
FROM   table1;

Query1 Query1

MeterReadingID	ReadingDate	MeterReading	BldgID_FK	YesterdayMeterReading	Useage
1​	1/1/2021​	10​	1​	​	​
2​	1/2/2021​	25​	1​	10​	15​
3​	1/3/2021​	45​	1​	25​	20​
4​	1/4/2021​	80​	1​	45​	35​
5​	1/1/2021​	100​	2​	​	​
6​	1/2/2021​	200​	2​	100​	100​
7​	1/3/2021​	350​	2​	200​	150​
8​	1/4/2021​	475​	2​	350​	125​

 

[namliam]

With all due respect to the suggestions posted, but No no no and once again no....
Dmax and Dlookup should be avoided like the plague and inline subselects are as bad as those two.

Assuming you take a measurement EVERY DAY, a simple self join can do the trick.
Building on what @MajP posted, it would be something like:

SELECT table1.meterreadingid,
       table1.readingdate,
       table1.meterreading,
       table1.bldgid_fk,
       SJ.meterreading AS YesterdayMeterReading,
       SJ.meterreading - table1.[meterreading]       AS Useage
FROM   table1 
join  table1 SJ on table1.bldgid_fk = SJ.bldgid_fk
                       and  table1.readingdate = SJ.readingdate - 1

"Challange" can be that you cannot use the query designer with this statement, it is pure SQL but should work just fine.

 

[Gasman]

With all due respect to the suggestions posted, but No no no and once again no....
Dmax and Dlookup should be avoided like the plague and inline subselects are as bad as those two.

Assuming you take a measurement EVERY DAY, a simple self join can do the trick.
Building on what @MajP posted, it would be something like:

SELECT table1.meterreadingid,
       table1.readingdate,
       table1.meterreading,
       table1.bldgid_fk,
       SJ.meterreading AS YesterdayMeterReading,
       SJ.meterreading - table1.[meterreading]       AS Useage
FROM   table1
join  table1 SJ on table1.bldgid_fk = SJ.bldgid_fk
                       and  table1.readingdate = SJ.readingdate - 1

"Challange" can be that you cannot use the query designer with this statement, it is pure SQL but should work just fine.

If SJ.meterreading is YesterdayMeterReading wouldn't it be

table1.meterreading - SJ.meterreading

 

[MajP]

Assuming you take a measurement EVERY DAY

And there lies your problem, because in most data sets the prior data is never guaranteed. So 9 times out of 10 "yesterday" means previous reading. And due to the scale of this I would think these readings are manual and not computerized, but maybe they are.

No no no and once again no...

And before we get overly pedantic lets keep things in perspective. This is a simple Access database with a novice user and 8 sites. That means 3k records a year. So after about 20 years from know when this query gets noticeably slow we can worry about efficiency.

 

[isladogs]

Just for info, Allen Browne has a very similar example for meter readings using SELECT TOP 1 and a subquery
Microsoft Access tips: Subquery basics (allenbrowne.com)

@namliam
Your SQL might work better with INNER JOIN rather than JOIN

 

[namliam]

Inner join is the same as a join, depending on if access accepts it or not...

You can add -2 -3 -4 -5 to the join(s) to ensure you find results...
Or even do a Max(readin) join date between today -1 and today -7

I do agree, performance probably wouldnt be an issue on such a small set.

 

[isladogs]

Inner join is the same as a join, depending on if access accepts it or not...

Precisely my point. It doesn't

 

[gemma-the-husky]

In general, I can't see the need to track daily usage on a form, which is hard to do anyway. eg, what happens if you forget to record readings for a date or two. Maybe all you really need is the difference between the lowest and highest reading in your selection, and an average over the total number of days. You can get these with very little work (and it doesn't matter then if you miss readings here and there.)

 

[isladogs]

Most of these methods including that by Allen Browne in my link, just compare the latest reading with the previous record for that client. The dates are irrelevant

 

[Gasman]

I actually measure my water consumption, but only monthly. and then only to the cubic metre.?

 

[plog]

Cubic metre? What was the point of Brexit? Ditch those oppressive EU units and return to imperial like us geniuses in the US and Iberia and Burma.

 

[isladogs]

Sorry to be rood but we're poles apart on units.
I really can't fathom why any countries still use imperial units despite acres of discussion. I'll get off my perch now

 

[Gasman]

When I was in college many years ago, a college friend's answer to a problem, was 100 times out, and so got no marks.
He complained that the decimal point was just a few digits out, but the digits were correct, to which the lecturer stated

'Mabbett, it is 100 times out, and if we were still using imperial units, the digits would not even resemble anything like the correct digits'

 

[Gasman]

Cubic metre? What was the point of Brexit? Ditch those oppressive EU units and return to imperial like us geniuses in the US and Iberia and Burma.

Yet in Vietnam, you used klicks

 

[isladogs]

BTW Iberia is a peninsula made up of two EU countries, Spain & Portugal, both of which use the metric system.
That should have been Liberia and that, along with Myanmar (Burma) are in the process of converting to the metric system.

Soon the USA will be in a imperial league if its own though perhaps even the USA will eventually inch towards the metric system

 

[Gasman]

Their pints and gallons are smaller than ours as well.

 

[isladogs]

I was working on getting those units into my next reply...
I suppose the US will say that since independence they are no longer in our back yard, have broken the chain so they can stand on their own two feet and do what they like

 

[gemma-the-husky]

BTW Iberia is a peninsula made up of two EU countries, Spain & Portugal, both of which use the metric system.
That should have been Liberia and that, along with Myanmar (Burma) are in the process of converting to the metric system.

Soon the USA will be in a imperial league if its own though perhaps even the USA will eventually inch towards the metric system

Whatever they do though, it will still be 10 yards in 4 downs, I think, Colin.
And I suspect a cricket wicket will remain 22 yards.