Need VBA help

[Bob]

I could use some help, ideas, code samples, whatever you might have in mind.
I have schools taking daily readings of their electric meters Monday through
Friday. The Meter_ID, Read_Date, Read_Time, Amt_kWh, and Amt_Kw are going to
table tbl_Meter_Reading. If I take this data into Excel I can extract an
average kWh usage for school days for a given period, I can take Monday's
reading, subtract an average school day (reading from Friday PM to Monday
PM) and thereby get an average usage over the weekend and I can take the
vacations and do the same. This is valuable information both for the kids
and school staff. Problem is it is a complicated process in Excel. I would
like to output it in a report but I don't have enough VBA experience to get
a good enough handle on it. Any ideas would be greatly appreciated.

 

[Allen Browne]

See:
Subquery basics: Get the value in another record
at:
http://allenbrowne.com/subquery-01.html#AnotherRecord

The example shows how to get the most recent previous reading in the table.
You can modify it to get the most recent reading that is at least 7 days
prior by adding to the WHERE clause in the subquery.

Once you have the current reading and the previous one, you can subtract one
from the other in a calculated query field.

 

[John Spencer]

More details are needed to help you.

One reading per day per meter or multiple readings per day per meter?

Is there always a reading for every weekday for every meter? ( or do
you exclude weekends, exclude holidays, exclude breaks - spring, summer,
etc)

What are your field types? Are Read_Date and Read_Time both datetime
fields?

What is the difference between Amt_kWh and Amt_Kw? That is what is
stored in those two fields? Is Amt_Kw a cumulative reading? Does it
ever "start over"?


The following is a query that should get the a current reading and the
last prior reading.

SELECT T1.Meter_ID
, T1.Read_Date
, T1.Read_Time
, T1.Amt_kWh
, T1.Amt_Kw
, T2.Read_Date
, T2.Read_Time
, T2.Amt_kWh
, T2.Amt_Kw
FROM tbl_Meter_Reading as T1
as T1 INNER JOIN tbl_Meter_Reading as T2
ON T1.Meter_ID = T2.Meter_ID
WHERE T2.Read_Date + T2.ReadTime =
(SELECT Max(Read_Date + Read_Time)
FROM tbl_Meter_Reading as T3
WHERE T3.Meter_ID = T1.Meter_ID
AND T3.Read_Date + T3.Read_Time <= T1.Read_Date + T1.Read_Time)


T1, T2, and T3 are just aliases to your table. Each "alias" is just a
reference to the same table, but allows SQL to operate as if there were
three identical copies of the table.

Adding the Read_Date and Read_Time together could give you erroneous
results. A better and more efficient structure would be to have the
date and time stored in one field. That way you would have the ability
to index the date and time and use it the index to give you much faster
results.



'====================================================
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
'====================================================