Calculating consumption

[HeshamHatem]

I have been trying for a very long time now to calculate the consumption of water and electricity in access as my tables has the date as the primary key and there is the meter readings daily inputs in the following columns.

how do i calculate the consumption easily?
i have tried the Dlookup but it fails when we have consecutive dates with the same readings.

I'm really frustrated as this has taken me quiet long time to sort out and it seems like a dead end to me.:banghead::banghead::banghead:

 

[The_Doc_Man]

Define consumption. (Not kidding...)

Are you looking for average daily usage, total usage for some time period, ... define the units in which you want this answer.

In general there are queries that can do this for you over a wide range of units, but if you don't define what you want, you won't know how to ask the right question.

 

[HeshamHatem]

I mean i have the date in one column, the daily readings in the following columns for electricity, water , gas and so on. What I want is to subtract to consecutive rows from the daily readings to get the consumption in another row.

Like in excel consumption would equal daily reading at day 2 - daily reading at day 1 ( B2=A2-A1) that is how it works in excel and I wanna do the same in access

 

[WayneRyan]

H,

Will This Work?
If it does, you can use this for weekly/monthly totals.
Substitute your table/column names.


tblUsage
========
UsageDate Datetime
Electricity Integer
Gas Integer
Water Integer

Select a.UsageDate, 
       (a.Electricity - b.Electricity) As Electricity_Usage,
       (a.Gas - b.gas) As Gas_Usage,
       (a.water = b.water) as Water_Usage
From   tblUsage as a Left Join tblusage as b on
         a.UsageDate = DateAdd(-1, "d", b.UsageDate)
Order by a.UsageDate

hth,
Wayne

 

[Acropolis]

Depends on how you want to show it really, but something like this?

Consumption = Dlookup("[Consumption]","table","[Date] = '"& format(dateadd("d",1,EarlierDate,"yyyy-mm-dd") &"'") - Dlookup("[Consumption]","table","[Date] = '"& format(EarlierDate,"yyyy-mm-dd") &"'")

That would give you the consumption between two consecutive days, you could put that into a query, and it should work - not tried it.

 

[HeshamHatem]

I have attached a sample of what i want done in access.

I haven't tried what was given to me yet but will do and get back to you guys and many thanks for your replies.

Please review the example in the excel sheet and tell me if it can be done by creating a function in VBA that can do it ?

 

[JHB]

Use a sub query, the link shows how to set it up.
http://allenbrowne.com/subquery-01.html

 

[Acropolis]

Assuming the spreadsheet is the actual table layout you have, then i don't think that's the best way to store the data myself.

Depending on how many meters you have, or may have in the future, you would either want to store the readings by the meter or the utility, you would find it better to do it by the meter serial, then if someone else has to enter readings for you, you know it goes against the right one, rather than having to know the order in which to put them.

ID
MeterID
ReadingDateTime
Reading

I have a similar thing were I store manual meter readings, and compare those readings and consumption against AMR data from the same meters, only I have thousands of meters in the DB across all the utilities, but I do run sub metering and monitoring projects for clients.

 

[HeshamHatem]

In fact I have about 20 meters only that are stored daily at the same time to avoid duplication in the entries.

the main issue is about doing the calculation itself as i have been looking for away around it for a very long time now even before sharing my issue on this forum.

you people are very helpful already.

Since you have experience in the issue i'm facing may you share with me how you deal with it on Access?

 

[Acropolis]

The dlookup above will work for you, however, as you have 20 meters, and are storing then all in the same line in the table, then you are going to have to have 30 formula's to calculate what you want, and if you want to add a new meter you'll need to alter the table layout and add new formula's, to my understanding that goes against the way in which a database should work.

You should ideally have you meters in one table, then you readings in other using the structure i suggested previously, that way you can easily start to do what you want, and if you add meters in the future it becomes easier etc.

At the moment if you want a report, how do you know which reading is for which meter? Using the column name isn't the best way of doing this.

I would suggest before you go any further with this, look into database structures and sort your data out, but if you want to push ahead as you are, then you can manipulate the dlookup i gave earlier and change the column names to those represent where your data is.

 

[HeshamHatem]

OK, I understand what you are saying and it is logical to a big extent.

but then you would mean that entering the meter reading data should be done after selecting the meterId from a combo box for example ???

but still if I did this, which is more realistic regarding adding more meters or removing meters, I would be facing the same problem with calculating the consumption.

I appreciate your support highly my friend but i hope you understand the fact that I'm not a professional in using access so if you may be more explanatory.

 

[Acropolis]

You could use a combo box, or a list box to select each meter, then have a text box with the date in and another for the reading.

Or you could have a button that creates an entry in the table for each meter with a 0 for the reading, or even leave NULL, then use a data sheet to go through and enter readings, filtered but he date for which you want to enter the readings. This would give the feel of a spreadsheet and you could just move down each row and enter the reading.

Depending on how you gather the readings in the first place could also present a different way of doing things. If the readings are emailed to you in a spreadsheet for example, which i know is common in shopping centres, where say the maintenance team collect the readings and email them to someone to enter, then you could write an import script and import the readings from the spreadsheet, this could be a good way as you could check against false readings, where it is lower than the previous reading for example.


How to calculate the consumption, depends on how you want to show the data, do you want to see it everyday, every week, every month, every quarter etc.

The basic premise, is subtract the earlier reading from the later reading, just depends on how you want to view that data as to how you go about it. If you want to look at a list everyday that shows you want each meter used then it's fairly simple, but there would be limitations, for example what would you do on a Monday if there are no meter readings for a Sunday, you wouldn't be able to see that consumption.

Explaining a little more around what you are trying to achieve and how you want to see it might be a starting point.

 

[HeshamHatem]

The meters are collected manually and handed to me in a paper form.

I have the readings everyday so I'm never going to face a missing day or a missing entry.

I need to see the consumption on daily basis and I'll generate a report to give me the data monthly.

My only issue is doing the row subtraction and now after going with the combo or list, i'll need to do it after being grouped by the meter ID.

How to do this ?? All I'm seeking is the formula to group them by MeterID and then subtract the reading at a date from the previous reading to get the consumption.

 

[Acropolis]

This should work as the basic premise behind the consumption calculation, exactly where you use it and how you use it will need tweaking slightly to match the scenario, for example where are you going to get the MeterID from and where are you going to get the latest date from, that depends on how you want to view the data, which you haven't said.

Consumption = Dlookup("[Reading]","table","[MeterID] = "& MeterID &" AND [ReadingDate] = '"& Format(LatestDate,"yyyy-mm-dd") &"'") - DLookup("[Reading]","table","[MeterID] = "& MeterID &" AND [ReadingDate] = '"& Format(DateAdd("d",-1,LatestDate),"yyyy-mm-dd") &"'")

 

[HeshamHatem]

I want to view it daily

as in meterID and next to it the consumption daily.

and I tried the dlookup before and it showed wrong values when it faces repeated readings. as you know you can face it sometimes when a certain area is closed and so the meter readings are constant for a while that's when the Dlookup fails.

 

[Acropolis]

If the dlookup is failing at that point, then you've got it wrong somewhere, or else it's the way your storing the data.

the dlookup just returns values from where you tell it to, based not eh criteria you enter. You are then taking away one value from another, ie the latest reading from an earlier reading, and showing the difference. If two readings are the same then it will show 0, (for example 10-10=0).

Are you doing anything else with this data, or is it purely for the meter reading and seeing consumption, a spreadsheet might be a better idea just to see the readings?

 

[isladogs]

Back in post 7 JHB gave you a link to Allen Browne's website where he explains exactly how to deal with calculating energy 'consumption' based on successive meter readings.

You need a normalised table with one reading only on each record.
Then use a subquery to do the calculations.
I strongly recommend you use his approach.

 

[gemma-the-husky]

Just as a general thought. If you have multiple readings on the same date, you need a way to distinguish the one you want.

with nothing else to go on, a dlookup on the data will return a random selection from the readings on that particular date.

 

[HeshamHatem]

Expr1: DLookUp("[Reading]","[Readings]","[MeterID] = " & [MeterID] & " AND [Date] = '" & Format([LatestDate],"yyyy-mm-dd") & "'")-DLookUp("[Reading]","[Readings]","[MeterID] = " & [MeterID] & " AND [Date] = '" & Format(DateAdd("d",-1,[LatestDate]),"yyyy-mm-dd") & "'")

That's how I typed the function and then it comes back asking to identify the LatestDate, I tried removing the parenthesis but it keeps adding it and asking for it.

Any Help !!!! :banghead::banghead:

 

[Acropolis]

You need to tell Access what the latest date is, LatestDate is a variable, which needs to have a value assigned to it.