troubleshooting Question

Electric Meter table: How do I calculate usage? Applies to timesheets, etc.

Avatar of intsup
intsupFlag for United States of America asked on
Microsoft Access
10 Comments5 Solutions1159 ViewsLast Modified:
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.
           
Join the community to see this answer!
Join our exclusive community to see this answer & millions of others.
Unlock 5 Answers and 10 Comments.
Join the Community
Learn from the best

Network and collaborate with thousands of CTOs, CISOs, and IT Pros rooting for you and your success.

Andrew Hancock - VMware vExpert
See if this solution works for you by signing up for a 7 day free trial.
Unlock 5 Answers and 10 Comments.
Try for 7 days

”The time we save is the biggest benefit of E-E to our team. What could take multiple guys 2 hours or more each to find is accessed in around 15 minutes on Experts Exchange.

-Mike Kapnisakis, Warner Bros