Dear Richard:
Well, Richard, I'll tell you what I do know.
I do know that I've tried various approaches to solving many database
problems. From some successes and failures, I've come to the conclusion
that the rules about how databases should be constructed are extremely
valuable. And these rules say not to store any derived values in tables.
The biggest reason for that is as follows: If you have a derived value, and
any of the stored values that are components of that value are changed, then
the derived value must immediately change as well.
I have actually written the code that will make a derived value follow any
database changes. It is about 5-10 times as much work as doing it
correctly, and it tends to be unreliable. It is too easy to forget one of
the things that might change and make the derived value incorrect.
For example, when you do this, how will you handle the case that a user
deletes the row containing the previous day's reading? How will you handle
it if the user then re-enters that row, but with a different value?
I'm telling you, it's a complex mess handling all these eventualities, if
you write the system incorrectly. However, if you simply derive all the
derived values at the moment you need them, then you won't have this
problem.
Everything in my 23 year's experience writing database software screams that
this is a really bad approach.
The kind of query you need is something I write several times a week, and
have done so hundreds of times. In my own project, this would not take me
15 minutes. I say that not to brag (there are many who post answers here
who have similar experience and capability) but to encourage you to learn
the best skills and to apply them properly. Very soon you'll see that this
becomes easy to handle, and that this is definitely the best way to handle
the situation.
As I see it, you're at a turning point in how you will develop as a database
programmer. If you listen to good advise, and put it into practice, you'll
be vastly better off.
Perhaps others who are experienced and read this will drop in and lend a
vote to what I'm telling you, so you'll see what I say is quite true, and
important. Because what I'm advising you is not my personal preference, but
a very common experience of virtually all who have advanced professionally
in this field.
If you'll expose for me the name of your table and the names of your
columns, and perhaps a bit of sample data, I'll write the query to do this.
I've done so dozens of times per year for 5 or 6 years now in this
newsgroup, and have used this as a tool to teach how this is done to dozens
of others, and with quite good success (I'm guessing more than 95%). You'll
probably be glad you did!
Tom Ellison
RWhittet said:
Dear Tom,
After talking about this I am wondering if there isn't a better approach
to
this. Lets say that I created an extra field in my table for each meter
reading, and used my calculated controls on the form to set the values of
these fields. I have played around with the set value macro before but I
haven't gotten it to work. If this is possible I think would rather go
this
route. The meters we use are pretty sophisticated, they are connected to
the
SCADA system, and we also have operators who record the readings daily.
Give
this some thought and let me know what you think and if you have any
experience with the set value macro I would appreciate that too.
Thanks,
Richard
:
Dear Richard:
Something about this makes me vary leary. So, the meter is never out of
order, and the reading never fails to take place?
Still, the query can be written in different ways, and each will react
differently if a reading is missing for any reason. But I'll go ahead
anyway.
I will use an INNER JOIN between two copies of the table. JOIN it ON the
column that identifies each meter. Filter it with so one table has the
reading date equal to Forms![Meter Usage]!Date and the other to the same
date minus 1. That's at least a starting point.
I don't know the names of your table and columns. Without that I cannot
be
more specific.
Any meter for which the two readings are not both present will be omitted
from these results. It doesn't matter what kind of failure may have
produced the missing data.
Frankly, I would have thought to prorate between the reading whenever
there
is a missing reading. That is, if the meter read 13 on the first of the
month, and 22 on the fourth, then the consumption on the second, or the
third, or the fourth would each be 3 units.
Before pushing such an idea, I'd use queries to investigate where there
is
any history of a meter reading being missing. A simple aggregate of
COUNT(*) for each meter, and a COUNT(*) of every DISTINCT Date value in
the
table would tell me if there are missing readings, and how common they
are.
Those who instruct us to perform tasks like this often mistake just how
reliable their data is. I wouldn't ever argue such a point except where
it
is historically demonstrable. Once you can prove the necessity for a
cautious approach, then management can be made ready to listen.
I hope you won't think I'm being intrusive. This is really the voice of
reason and experience.
Tom Ellison
Thanks for the quick responses. There will never be a day when the
meters
are not read. The only case of an unread meter would be if that
pipeline
was
taken offline for repairs. And, this has happened. The ultimate goal
is
to
create a montly sum of the total amount of water through each meter to
be
used for billing, budgeting, predictions, etc. But, my boss created a
spreadsheet years ago that has been used ever since, so I am trying to
duplicate it, in a sense. It is important that these calculations be
accurate. The user pulling the report will select the beginning and
ending
date. The report itself is designed as a monthly report.
This is the code I have used in my form to show the previous meter
reading.
=DLookUp("[South Recycle]","[Meter Usage]","[Date] = Forms![Meter
Usage]![Date]-1")
Richard Whittet
:
Dear Richard:
Before helping prepare a way to query this, I want to check certain
facts.
You say, "These meters are read everyday" Does that mean it is never
the
case that a day goes by in which no meters are read (a holiday for
example)?
Does it mean that it is never the case that a meter is skipped?
If a day is skipped, like a holiday, then are you going to give
results
for
a 2 day period instead? If a meter is skipped, will you skip
reporting
that
meter for 2 days (the day in which it has the current reading, then
the
day
in which it has the previous reading).
Will the user pulling this report select the Day 1 date? Will you
then
report only those meter that were read on the chosen date AND on the
previous day?
Realistically, real world data has holes in it. I'm trying to get our
thinking together on whether such holes will EVER happen, and what you
want
to do in that case.
In order to do what you want, you need to use two copies of the table
in
the
same query. One feature of doing this is to alias them, so you can
reference them separately.
I need some detailed, precise information on the questions I asked
before
I
can begin to craft any solution. OK?
I'm going to suggest one possible solution to the problem. Let's say
you
have a control on a form with the more recent date in it. We could
then
build a query that filters to that date and to the immediately
preceeding
calendar date. Any meter that does not have BOTH readings would be
excluded
from the results. That's a simple, but perhaps not most desirable way
to
handle it.
Tom Ellison
I have run into a problem in my query. I am trying to run
calculations
on
water meters. I have been able to calculate the information I need
on
my
forms, by using DLookup, but it's a bit more tricky to do in the
query.
These meters are read everyday, and the calculations needed are also
daily
as
they convert the value into million gallons per day. The
calculation
is
as
follows;
(Day 2 meter reading - Day 1 meter reading)/1000
All of the readings are stored in the same table, so the biggest
problem
is
referencing the value from the previous record. I have also been
able
to
create the calculations in my reports, but I'm unable to use a
monthly
average or sum on a calculated field in a report. Any solutions or
suggestions would be greatly appreciated!
