Re: Help with calculations in a query

Tech-Archive recommends: Repair Windows Errors & Optimize Windows Performance

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" <RWhittet@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in message
news:7944BC86-4067-43DD-9DD7-4BE6BA16465D@xxxxxxxxxxxxxxxx
> 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
>
> "Tom Ellison" wrote:
>
>> 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
>>
>>
>> "RWhittet" <RWhittet@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in message
>> news:07391C11-852B-40FF-A9DA-047519B6D4A5@xxxxxxxxxxxxxxxx
>> > 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
>> >
>> > "Tom Ellison" wrote:
>> >
>> >> 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
>> >>
>> >>
>> >> "RWhittet" <RWhittet@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in message
>> >> news:06106176-49F2-4CC5-9FBE-82844CCCA68E@xxxxxxxxxxxxxxxx
>> >> >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!
>> >> >
>> >> > Richard Whittet
>> >>
>> >>
>> >>
>>
>>
>>


.

Relevant Pages

  • Re: Help with calculations in a query
    ... as I don't have the database at home. ... > deletes the row containing the previous day's reading? ... Lets say that I created an extra field in my table for each meter ... >>> differently if a reading is missing for any reason. ...
    (microsoft.public.access.queries)
  • Re: Help with calculations in a query
    ... Each row is the reading for one meter on one day. ... "Tom Ellison" wrote: ... > Dear Richard: ...
    (microsoft.public.access.queries)
  • Re: Canned mackeral or sardines - breathalyzer
    ... haven't been on this diet for long enough to retest. ... accuracy of those tests, and your meter, and tend to repeat ... what my dietition recommended. ... The other day I got a 142 post meal reading on my Reli-on. ...
    (alt.support.diabetes)
  • Re: Application Speed Problem - VERY weird
    ... MDE front ends not MDB ... If the DB is reading in a lot of records, ... The data is linked with the standard Access "link ... add that we have only ever used the database on Access 2K. ...
    (microsoft.public.access.tablesdbdesign)
  • Re: Help with calculations in a query
    ... Table - Meter Usage ... South Recycle Pond Switch ... > deletes the row containing the previous day's reading? ... >>> differently if a reading is missing for any reason. ...
    (microsoft.public.access.queries)