Project of meterreadings and consumptioncalculation

Question

Hi! I have for quite som time tried to learn Access. Im only at the base to create tables and so.The project I have been trying on and off, for some years now is a database that will keep track of Estates, Persons (owning the estate), Every estate has a submeter wich is read 2 times/year, and there is one master meter.Now I am trying to get a query to calculate the consumption of each estate by reading the last entry minus the second latest. And then take the mastermeter minus the sum of all submeters to calculate if there is a differens.But I can't get it to work, tried some tips I've got but can't just make it.&nbsp;The tables I got in my testbench is:tblPersonPersonID,&nbsp; ForName, LastName1&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;John&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; Doe2&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;Lisa&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; Niska3&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;Gary&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; Cooper4&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;Austin&nbsp; &nbsp; &nbsp; &nbsp; Powers&nbsp;tblEstateEstateID,&nbsp; EstateName1&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;Mastermeter2&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;House0013&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;House0034&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;House004&nbsp;tblEstatePersonEstatePersonID,&nbsp; Estate ID(lookup from tblEstate),&nbsp; PersonID(lookup from tblPerson)1&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;House001&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;John Doe2&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;House003&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; Lisa Niska3&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;House004&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; Gary Cooper4&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;House003&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; Austin Powers&nbsp;tblReadingReadingID,&nbsp; ReadingDate, EstateID,&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; Reading1&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;2023-01-01&nbsp; &nbsp; MasterMeter&nbsp; &nbsp; &nbsp; &nbsp;2302&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;2023-01-01&nbsp; &nbsp; House001&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; 103&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;2023-01-01&nbsp; &nbsp; House002&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;1004&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;2023-01-01&nbsp; &nbsp; House003&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; 1155&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;2023-02-01&nbsp; &nbsp; MasterMeter&nbsp; &nbsp; &nbsp; &nbsp;2606&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;2023-02-01&nbsp; &nbsp; House001&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; 207&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;2023-02-01&nbsp; &nbsp; House002&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;1108&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;2023-02-01&nbsp; &nbsp; House003&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;120&nbsp;....I tried to follow a direction in NorthWind database to get de second latest date for an order and calculate the days between. But I havent been able to convert&nbsp; it for my needs.&nbsp;The query consist of&nbsp;ReadingID, ReadingDate, Estate, Reading, Prior reading, Consumption.Prior reading has this code:Prior Reading: (SELECT MAX([Reading])FROM [tblReading] AS [Old Reading]WHERE [Old Reading].[Reading]&lt;[tblReading].[Reading]AND [Old Reading].[ReadingID]=[tblReading].[ReadingID])&nbsp;And consumption has this code:Consumption: [Reading]-[Prior Reading]&nbsp;I got the fields but no values in the fields. Am I thinking totaly wrong?I want to use theese numbers to be able to make an invoice to each house.&nbsp;Hope you unde&nbsp;&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;

arnel_gp · Answer

andersammediase&nbsp;&nbsp;See Query1 and Query3

andersammediase · Answer

arnel_gp&nbsp;Thanks a lot! Now I will try to read and understand what the different parts do.And then start working on an invoice for each estate based on those numbers.&nbsp;Another question. If I have waterprice in a table to take the cunsumption times waterprice, lets say it is£12 at this reading.If we later raise the prise to £14 will that affekt the old invoices to, which are already billed?Should I make a tblWaterPrice&nbsp; WaterpriceID, WaterPrice, FromDate, ToDate?To be able to keep track and to have the old ones show the right price if someone asks for a copy?&nbsp;Am I thinking wrong here?&nbsp;

arnel_gp · Answer

andersammediase&nbsp;you can use FromDate and ToDate to your WaterPrice.you can also use only one Date, EffectiveDate (see the tblWaterPrice on the demo).see Query1 and Query4 on how to get the WaerPrice.

Forum Discussion

Project of meterreadings and consumptioncalculation

3 Replies