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
I hope I was able to convey my problem concicesly and logically but, if not, let me know and I'll try to expound.
And please be gently, I'm familiar with the graphical functions of Access but don't know anything about the SQL programming behind the scenes.