Can you calculate energy consumption in an Access Query?

[Jaap]

Hello,

I like to know there is a solution to calculate energie consumption in an Access Query?





Regards


Jaap

[Micron]

See if this is what you need (example is actually based on meter readings).
http://allenbrowne.com/subquery-01.html

[Jaap]

See if this is what you need (example is actually based on meter readings).
http://allenbrowne.com/subquery-01.html

Thanks for the example you sended me.

This example is to difficult for me, I was hoping something to do in an Query with: Totals, Min and Max.

Jaap

[orange]

I suggest you post a copy of your database in order for someone to review your code or set up a query(ies) to illustrate the approach with your data.
The example micron pointed you to is all about difference in meter readings -study the example carefully. It definitely applies to your issue.

Break your requirement into steps.
Post your sql.

[Welshgasman]

Thanks for the example you sended me.

This example is to difficult for me, I was hoping something to do in an Query with: Totals, Min and Max.

Jaap

SELECT MeterReading.ID,
MeterReading.ReadDate,
MeterReading.MeterValue,
 (SELECT TOP 1 Dupe.MeterValue                 
 FROM MeterReading AS Dupe                     
 WHERE Dupe.AddressID = MeterReading.AddressID 
   AND Dupe.ReadDate < MeterReading.ReadDate   
 ORDER BY Dupe.ReadDate DESC, Dupe.ID)          AS PriorValue

That is a query?

[xps35]

NL
Als je alleen die ene tabel hebt, kan je dit volgens mij beter oplossen in Excel.
Het punt is dat Access in tegenstelling tot Excel niet zoiets kent als de "vorige regel". Je moet dat oplossen in termen van de vastgelegde gegevens. Dus iets als "vind het record met de hoogste datum kleiner dan de datum uit het huidige record". Om dat te realiseren heb je inderdaad ingewikkelde oplossingen nodig, maar het kan wel.

​EN
If you only have that one table, I think you can solve this better in Excel.
The point is that, unlike Excel, Access has no such thing as the "previous line". You have to solve that in terms of the data captured. So something like "find the record with the highest date less than the date from the current record". To achieve this you will indeed need complicated solutions, but it is possible.

[Jaap]

NL
Als je alleen die ene tabel hebt, kan je dit volgens mij beter oplossen in Excel.
Het punt is dat Access in tegenstelling tot Excel niet zoiets kent als de "vorige regel". Je moet dat oplossen in termen van de vastgelegde gegevens. Dus iets als "vind het record met de hoogste datum kleiner dan de datum uit het huidige record". Om dat te realiseren heb je inderdaad ingewikkelde oplossingen nodig, maar het kan wel.

​EN
If you only have that one table, I think you can solve this better in Excel.
The point is that, unlike Excel, Access has no such thing as the "previous line". You have to solve that in terms of the data captured. So something like "find the record with the highest date less than the date from the current record". To achieve this you will indeed need complicated solutions, but it is possible.

Beste Peter, bedankt voor het snelle antwoord.

Dan wil ik je toch vragen of het werken met een 2e Tabel een oplossing kan zijn svp?

Groeten

Jaap

EN
Dear Peter, thank you for the quick answer.
Then I would like to ask you whether working with a 2nd Table could be a solution, please?

[Welshgasman]

To achieve this you will indeed need complicated solutions, but it is possible.

Hasn't Allen Browne already given that solution?

[xps35]

Ik weet niet wat je in die tweede tabel wilt zetten. Met de huidige tabel is niets mis. Wat dat betreft heeft een tweede tabel dus geen zin en wordt het alleen maar ingewikkelder.Zoals anderen al schreven is het op te lossen. Alleen ziet het er ingewikkeld uit.

I don't know what you want to put in that second table. There is nothing wrong with the current table. In that respect, a second table is pointless and it will only become more complicated. As others have already written, it can be solved. Only it looks complicated.

[Welshgasman]

It is complicated, due to the fact that that table is not normalized?

So not being normalized, Excel would be a better option.

[xps35]

It is complicated, due to the fact that that table is not normalized

That is interesting! What should the table look like?

[CJ_London]

this is the table I use to record the readings


And my sql to determine values is

SELECT 
  A.MeterFK, 
  A.ReadingDate, 
  A.Reading, 
  B.ReadingDate, 
  B.Reading,
  [a].[readingdate]-[b].[readingdate] AS days, 
  [A].[reading]-[b].[reading]-(100000*([a].[readingdate]=#2020-11-12#)) AS used, 
  choose(a.meterfk+1,1,11.30585333) AS ConvFactor, 
  ([used]*convFactor)/[days] AS perday, 
   [perday]*365 AS peryear


FROM hseReadings AS A INNER JOIN hseReadings AS B ON (A.MeterFK = B.MeterFK) AND (A.ReadingDate > B.ReadingDate)

WHERE (((A.MeterFK)<=1) AND ((B.Estimate)=False) AND ((A.Estimate)=False))

ORDER BY A.MeterFK DESC , A.ReadingDate DESC , b.readingdate DESC;

This compares all readings from a meter with all previous readings so you can see at a glance usage over the last month, 2 months, years, whatever and compare with same month last year, current 12 months with previous 12 months etc. Could easily have additional criteria to limit this.

The conversion factor is used to determine kw from the usage based on the meter reading

The used value has an additional calculation adjustment due to the meter 'rolling over' from 100,000 back to zero.

[Welshgasman]

That is interesting! What should the table look like?

My thoughts would be a field to hold the type of reading. That would hold the ID from the MeterTypes table.
Then the sub query would also check the meter type.

[Jaap]

this is the table I use to record the readings


And my sql to determine values is

SELECT 
  A.MeterFK, 
  A.ReadingDate, 
  A.Reading, 
  B.ReadingDate, 
  B.Reading,
  [a].[readingdate]-[b].[readingdate] AS days, 
  [A].[reading]-[b].[reading]-(100000*([a].[readingdate]=#2020-11-12#)) AS used, 
  choose(a.meterfk+1,1,11.30585333) AS ConvFactor, 
  ([used]*convFactor)/[days] AS perday, 
   [perday]*365 AS peryear


FROM hseReadings AS A INNER JOIN hseReadings AS B ON (A.MeterFK = B.MeterFK) AND (A.ReadingDate > B.ReadingDate)

WHERE (((A.MeterFK)<=1) AND ((B.Estimate)=False) AND ((A.Estimate)=False))

ORDER BY A.MeterFK DESC , A.ReadingDate DESC , b.readingdate DESC;

This compares all readings from a meter with all previous readings so you can see at a glance usage over the last month, 2 months, years, whatever and compare with same month last year, current 12 months with previous 12 months etc. Could easily have additional criteria to limit this.

The conversion factor is used to determine kw from the usage based on the meter reading

The used value has an additional calculation adjustment due to the meter 'rolling over' from 100,000 back to zero.

Thank you all, I will look to the example of CJ_London.

I also have to figure out how this all is working, this will take a lot of time, but it's worth it.

Jaap

[Welshgasman]

this is the table I use to record the readings


And my sql to determine values is

SELECT 
  A.MeterFK, 
  A.ReadingDate, 
  A.Reading, 
  B.ReadingDate, 
  B.Reading,
  [a].[readingdate]-[b].[readingdate] AS days, 
  [A].[reading]-[b].[reading]-(100000*([a].[readingdate]=#2020-11-12#)) AS used, 
  choose(a.meterfk+1,1,11.30585333) AS ConvFactor, 
  ([used]*convFactor)/[days] AS perday, 
   [perday]*365 AS peryear


FROM hseReadings AS A INNER JOIN hseReadings AS B ON (A.MeterFK = B.MeterFK) AND (A.ReadingDate > B.ReadingDate)

WHERE (((A.MeterFK)<=1) AND ((B.Estimate)=False) AND ((A.Estimate)=False))

ORDER BY A.MeterFK DESC , A.ReadingDate DESC , b.readingdate DESC;

This compares all readings from a meter with all previous readings so you can see at a glance usage over the last month, 2 months, years, whatever and compare with same month last year, current 12 months with previous 12 months etc. Could easily have additional criteria to limit this.

The conversion factor is used to determine kw from the usage based on the meter reading

The used value has an additional calculation adjustment due to the meter 'rolling over' from 100,000 back to zero.

I actually use Excel for mine.