My question is very similar to this previously posted question. The difference is that the solution posted assumes that meter readings are taking monthly. In my situation the meters are read at different intervals daily, weekly, or monthly. How can I take the two most current readings of several different meters and find the usage of each?
Title:
Electric Meter table: How do I calculate usage? Applies to timesheets, etc.
Bookmark:
Question: I have a couple of buildings with a total of 31 electric meters and I send out electric bills every month based on their KWH usage over the month. Right now I'm using Excel to calculate usage and then mailmerge to create an electric bill. Using excel I have to create a new column every single month containing columns for the previous reading date, previous reading, current reading date, current reading, then calculatings based on that data.
I want to start using MS Access and have the meter readings contained in one record. Unfortunately, I'm having trouble with the calculations between records (vertical calculation)
I'd like my Access database to have the following Field Names (columns) with sample data:
PK ReadDate ReadingMeter1 ReadingMeter2 ReadingMeter3 ... ... ... ReadingMeter31
1 12/24/05 12345 67890 23456 7891
2 01/24/06 13456 67901 23567 8012
3 02/23/06 13567 68123 23678 8123
I have a couple of questions:
1. is this table design appropriate for keeping and calculating meter readings? note: i have to appendquery the meter usage calculations to another table in order to mailmerge them or export to Quickbooks (see item 2 below) and i'd rather automate the process instead of having to add columns every month.
2. how do i create a query that automatically takes the meter readings for every meter on the latest date (in this case 02/23/06) and subtracts the next to last date (in this case 01/24/06) and appends to a table as the following:
Meter PreviousReadDate CurrentReadDate PreviousReading CurrentReading Difference
1 01/24/06 02/23/06 13456 13567 111
2 01/24/06 02/23/06 67901 68123 222
3 01/24/06 02/23/06 23567 23678 111
...
31 01/24/06 02/23/06 8012 8123 111
Start Free Trial
