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

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.
           
intsupAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

puppydogbuddyCommented:
Your table design should be like this:
PK , ReadDate, MeterNo, MeterReading
You can index/sort by ReadDate/MeterNo and/or by MeterNo/ReadDate as needed

the following link will show you how to retrieve previous reading via query:
             http://support.microsoft.com/?kbid=208953

Hope this helps.
MSuchoffCommented:
I would strongly advise you to avoid building the readings table "out" by adding columns for each meter.

I built a quick sample based on my understanding of your needs and it did what I think your desired output is. This is what it consists of (explanatory notes at bottom):

   TABLE: METERS
    FIELD: METER_ID :  AUTONUMBER, PK
    FIELD: METER_NAME: TEXT (50)

   TABLE: METER_READINGS
    FIELD: READING_ID: AUTONUMBER, PK
    FIELD: READING_DATE: DATE/TIME
    FIELD: METER_ID: NUMBER
    FIELD: READING: NUMBER

QUERY: (YOU CAN COPY AND PASTE THIS INTO A QUERY BY SWITCHING TO SQL VIEW IN THE QUERY DESIGNER SCREEN)

SELECT MRA.METER_ID, MA.METER_NAME, CDate([ENTER BILLING DATE (MM/DD/YYYY)]) AS BILLING_DATE, Sum(IIf(Month([READING_DATE])=Month([BILLING_DATE]),[READING],0)) AS CURRENT_READING, Sum(IIf(Month([READING_DATE])=Month([BILLING_DATE])-1,[READING],0)) AS PREV_READING, [CURRENT_READING]-[PREV_READING] AS DIFFERENCE
FROM METER_READINGS AS MRA INNER JOIN METERS AS MA ON MRA.METER_ID = MA.METER_ID
GROUP BY MRA.METER_ID, MA.METER_NAME, CDate([ENTER BILLING DATE (MM/DD/YYYY)]);

Hope this helps,
Mike



Here's the data in METERS:
METER_ID      METER_NAME
1      METER 1
2      METER 2
3      METER 3
4      METER 4
5      METER 5

Here's the data in METER_READINGS:
READING_ID      READING_DATE      METER_ID      READING
1            2/11/2006      1            11000
2            2/12/2006      2            12000
3            2/13/2006      3            13000
4            2/14/2006      4            14000
5            2/15/2006      5            14500
6            3/11/2006      1            15000
7            3/12/2006      2            13000
8            3/13/2006      3            18000
9            3/14/2006      4            16800
10            3/15/2006      5            17654
11            4/11/2006      1            16000
12            4/12/2006      2            14000
13            4/13/2006      3            19000
14            4/14/2006      4            17800
15            4/15/2006      5            18654
16            1/11/2006      1            10000
17            1/12/2006      2            11000
18            1/13/2006      3            12000
19            1/14/2006      4            12800
20            1/15/2006      5            13654

When you run the query, it prompts you for a billing date (ie 03/01/2006) and produces this output(sorry about the wrapping):
METER_ID      METER_NAME      BILLING_DATE      CURRENT_READING      PREV_READING      DIFFERENCE
1            METER 1            3/1/2006      15000            11000            4000
2            METER 2            3/1/2006      13000            12000            1000
3            METER 3            3/1/2006      18000            13000            5000
4            METER 4            3/1/2006      16800            14000            2800
5            METER 5            3/1/2006      17654            14500            3154

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
MSuchoffCommented:
Oops. I hadn't refreshed my screen before submitting my comment, so I didn't see puppydogbuddy's comment. Didn't mean to look like I was stealing his suggestion. Man does my comment look waaaay too long!  :-)

I like the subquery approach as suggested in the msdn post. Leaner and cleaner.
OWASP Proactive Controls

Learn the most important control and control categories that every architect and developer should include in their projects.

dqmqCommented:
This query gives the general form for read history of five dial meters.  Notice, it handles rollovers in case the meter goes past zero.  It also eliminates the requirement that consequtive reads must be in adjacent calendar months. For example, it would be OK to have reads on DEC 1 and DEC 31 or  JAN 31 and MAR 1.  
Were it my design, I would be proactive and add #dials and multiplier to the METER table and use those in the DIFFERENCE (USAGE) calculation.  I would also recommend a unique index on METER_ID + READ_DATE to prevent duplicate reads on the same day.


SELECT
   MA.METER_ID
 , MA.METER_NAME
 , MRA.READING_DATE as CURRENT_READ_DATE
 , MRB.READING_DATE AS PREVIOUS_READ_DATE
 , MRA.READING AS CURRENT_READING
 , MRB.READING as PREVIOUS_READING
 , IIF(MRB.READING >= MRA.READING, MRB.READING - MRA.READING,  MRB.READING - MRA.READING + 100000) as DIFFRERENCE
FROM
  METERS AS MA
, METER_READINGS AS MRA
, METER_READINGS AS MRB

WHERE
       MRA.METER_ID = MA.METER_ID
 and MRB.METER_ID = MA.METER_ID
 and MRB.READING_DATE =
    (SELECT MAX(MRC.READING_DATE)
     FROM METER_READINGS AS MRC
     WHERE MRC.METER_ID = MRA.METER_ID
         AND MRC.READING_DATE < MRA.READING_DATE)
GRayLCommented:
PK , ReadDate, MeterNo, MeterReading

If your table reads as above:

SELECT
a.ReadDate,
a.MeterNo,
a.MeterReading,
(SELECT Max(b.MeterReading) FROM myTable AS b WHERE b.MeterNo = a.MeterNo and b.ReadDate<a.ReadDate) as PrevReading,
a.MeterReading-PrevReading as Difference
FROM myTable AS a WHERE
Format(a.ReadDate,"yy-mm") = Format(Date(),"yy-mm")
ORDER BY a.ReadDate, a.MeterNo;
intsupAuthor Commented:
Thanks for the quick replies!  I'm hoping to have this implemented by the next billing period in 3 weeks.  I'll take the weekend to try out your suggestions and get back with you all next week for any fine tuning..

thanks again!
intsupAuthor Commented:
using the info gathered here it looks like i've got the problem 95% solved, thanks!

i have a couple of questions & comments, though.

1.  to puppydogbuddy:  i had seen that microsoft article, unfortunately, when i tried to replicate it on my machine, i hit a roadblock at the alias creation so i was unable to use it.

2. to MSuchoff:  i like the ability to dictate what month and year to perform the analysis on since i might want to look up a previous bill.  i hadn't even thought of that before..   trouble is, i need to have both the readdate and previous readdate show up in the query.   can your query design show both dates?

3.  to DQMQ:  i'm afraid your solution gives me the difference for ALL month instead of the difference between two months.  it may be a typo in the query design but since i'm not very good ad SQL i'd be unable to debug it

4.  to GRAYL:  your query design actually met my original request (before i saw msuchof's date selection option).  can you tell me how to add the b.ReadDate (the previous reading date) to the query column? (i've tried playing around with the SQL but haven't been able to make it show).  also, i entered records even for a future date on 4/24/06 but the query only returned data up to 03/24/06.  i assume this is by design since changing my computer's system date to April allowed me to see the comparison between april's reading and march's reading.  is it possible to add an inquiry like MSuchof's design so I can specify what date to use as the a.ReadDate?

thanks!
puppydogbuddyCommented:
Instup,
Below is the SQL using the Microsoft KB article methodology for the query that extracts what you need for billing purposes, including deriving the previous reading.  Just paste this in the SQl view of your query, save and execute it.

Table1 in the query is the alias table (essentially like a temporary internal copy of the MeterTable created just to derive the previous reading).  The alias table was created by right clicking anywhere on the MeterTable, selecting properties from the shortcut menu, and assigning Table1 as the alias name.  You could have just as easily assigned MeterTableAlias as the Alias name.  If you are interested in pursuing this methodology, the SQL should be modified to account for a meter rollover as DQMQ suggested. In order to modify it, I would need more info about the reading such as the Capacity (the max reading)before meter rolls over.
.............................................................................................................................

SELECT MeterTable1.pk, MeterTable1.ReadDate, MeterTable1.MeterNo, MeterTable1.MeterReading, (Select Max(MeterReading) from MeterTable Where MeterReading < MeterTable1.[MeterReading]) AS PrevMeterReading, [MeterReading]-[PrevMeterReading] AS Difference
FROM MeterTable AS MeterTable1;
GRayLCommented:
You have to call up a second instance of the same table.  In the query pane of the Query Design Window, right click, Show Table, select the tablename, in the query pane right click, select table, select the same table, right click the new table and change the Alias to b.
dqmqCommented:
Despite my dyslexia (I got the "Current" and "Previous" column headings reversed), I was trying to give you a general query for read history. So, yes it includes all billing periods.  If you want to see just a single billing period then just add the appropriate condition to the end:

    and MRB.READING_DATE between <YourFromDate> and <YourToDate>

or if you want the just last billing period, add this condition:

     and MRB.READING_DATE = (Select Max(READING_DATE) from METER_READINGS AS MRM
         WHERE MRM.METER_ID = MRB.METER_ID)

or store the general SQL in a re-usable query called READ_HISTORY, then you can do like:

   Select * from READ_HISTORY where CURRENT_READ_DATE >= #03/01/2006#




It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.