Reference entry of record in same table, eg "previous" record


#1

I want to track meter readings for electricity and water consumption.

The relevant columns are:

  • Date of the reading
  • Reading (eg. kWh, m^3)

I want to calculate how much kWh or m^3 was used between meter readings and then how much was used per day.
In excel or similar systems I referenced the Date and Readings Entries for the previous Record to calculate the difference in kWh or m^3 and Time. I’m having problems modelling the same concept here. How would you do it?

Or do I need another table for my analysis? If so: can I automatically populate the Date column in the Analysis Table from the Readings Table?

Thanks!


#2

Hi @Ronja_Deisel

I use Currency field with custom currency symbol for example kWh.
Just set your symbol to what you need and it will be placed befor the reading number.

To calculate the difference between the last and actual readings, it needs an other table with linked columnt to dates and lookup columns to the readings (both actual and last)
This is my solution for this problem.


#3

That’s a good idea about the currency, I’ll do that!

And thanks for the solution with the two tables, too. I was thinking too much in spreadsheet terms to think of that!
Thanks!


#4

This is what I’m looking for also - could you post a sample?


#5

Hi Peter!
I shared a base with you. You can see the electricity meter checks by date and electricity calculations (difference, average…) the calculated usage is linked back to electricity table. The checked unit field is a currency filed with customized unit.

Sample linked record to latest record with customized unit


#6

Thanks much!

Peter Kaseman-WoldGoodland Tree Works, Inc.
608-221-9565**www.goodlandtreeworks.com
http://www.goodlandtreeworks.com/


Calculate difference between values on separate rows