Help with calculations in a query

[RWhittet]

Dear Tom,

I tried the query and it did work, however I am going to set up my table
like you have said so that I have done it correctly. I am going to work on
this for a while and I will get back to you later if that is okay. Will the
same query work for the new table if I have set the table up like you
suggested?

Richard

Dear Richard:

When it comes to being able to make an analysis like the one you mention, it
really is much better to have 20 rows for 20 meters than just on row for 20
meters.

But the real test is this. What would you have to do to add a new meter?
If the answer is that you'd have to add a new column to a table and change
all the forms and reports, then you have a severe normalization problem.

For your more immediate needs, did you try the query I proposed?

Tom Ellison

Dear Tom,

I'm not real sure if we are on the same page right now. I know that I
have
some normalization issues in this database, particuliarly with the naming,
but I thought I had the tables set up correctly. We've been using the
database for about three years and it's done more than anyone expected it
to.
I want to make sure I am understanding what you said correctly, so for
11/26/2005, I would have some 20 rows of data? The way I have understood
it
is that I should have one record for all of the day's meter readings, not
20.

Dear Richard:

Looking at your data, I had already suspected you have a large amount of
normalization problem.

To be effective, your database should have one row for each combination
of
meter and date. There are excellent ways of doing this.

Typically, you would have a table of all the meters involved. Each meter
would be uniquely named.

You could temprorarily fix this by creating a UNION query that creates
this
appearance.

For the time being, then, all you comparisons are between a single record
for all of today's readings and another record for all of yesterday's
readings. The foundational query for this would be:

SELECT *
FROM [Meter Usage] MU0, [Meter Usage] MU1
WHERE MU0.Date = #11/26/05#
AND MU1.Date = #11/25/05#

This puts all the columns of 2 of your records together. You can then
simply add a difference calculation between them.

This could become complicated by the 255 column limitation for any table
or
query. Probably you wouldn't have much problem for now, but if you have
more and more meters then it will become more and more of a problem.

As it is, if you needed to add a meter you'd need to add a new column to
the
table for its data and then change all the reports and forms working with
this. That's really the hard way of doing it.

Tom Ellison


Dear Tom,

The relationship between the rows is determined by the date field. I
apologize if the way I pasted the information is confusing. If you
would
prefer, I could figure out an easier way to get this information to
you.
Each row is the reading for one meter on one day. If you look to the
far
left column of the message, you will see the field names, such as:LT
24,
LT
18, LT 16 and Filter 14-23. Each one of those represents a meter and
the
number that follows them is the meter reading for that day.

Richard

:

Dear Richard:

Looking at the two records you provided, the first question is this:
On
which column(s) is the relationship between the rows determined? How
can
I
tell that these two rows are for the same meter? Is each row the
reading
for one meter on one day?

Tom Ellison

Dear Tom,

I see that I forgot to give you some data. So I am reposting it,
sorry.

Table - Meter Usage
ID # - Primary Key - 1719
Date - 8/17/05
Day - 230
Employee Name - Richard Whittet
LT 24 - 0.49
LT 18 - 1.154
LT 16 - 2.592
CW 20 - 1.477
LT Mariana - 0.244
LTCW 42 - 11.396
LT 8 - 0.195
North Carter - * 499246
Comments
12 Inch RW - * 18635057
Turnout #1 - 0.98
North Recycle - * 488734
Filter 14 - * 137059
Filter 15 - * 122433
Filter 16 - * 3282807
Filter 17 - * 2755124
Filter 18 - * 2740611
Filter 19 - * 1275411
Filter 20 - * 1028674
Filter 21 - * 1136286
Filter 22 - * 1222160
Filter 23 - * 1072176
14 Inch BW - * 7540
South Recycle - * 1318149
South Recycle Pond Switch - yes
South Recycle Pond - 1
North Recycle Pond Switch - no
North Recycle Pond - 1
Demand High - 8.450
Demand Low AM - 5.550
CB-2 Minimum Level - 3.50
CB-3 Minimum Level - 4.21
3-4 Flowrate - 2.10
5-8 Flowrate - 4.56

Table - Meter Usage
ID # - Primary Key - 1720
Date - 8/18/05
Day - 231
Employee Name - Richard Whittet
LT 24 - 0.53
LT 18 - 1.235
LT 16 - 2.844
CW 20 - 1.427
LT Mariana - 0.315
LTCW 42 - 11.926
LT 8 - 0.213
North Carter - * 500049
Comments
12 Inch RW - * 18639524
Turnout #1 - 1.915
North Recycle - * 492855
Filter 14 - * 138449
Filter 15 - * 123823
Filter 16 - * 3284193
Filter 17 - * 2756494
Filter 18 - * 2741978
Filter 19 - * 1276836
Filter 20 - * 1030078
Filter 21 - * 1137684
Filter 22 - * 1223561
Filter 23 - * 1073563
14 Inch BW - * 7829
South Recycle - * 1319259
South Recycle Pond Switch - no
South Recycle Pond - 3
North Recycle Pond Switch - no
North Recycle Pond - 1
Demand High - 8.689
Demand Low AM - 4.689
CB-2 Minimum Level - 3.75
CB-3 Minimum Level - 4.15
3-4 Flowrate - 2.05
5-8 Flowrate - 4.50
Thanks again for your assistance.
Richard





:

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.

 

[Tom Ellison]

Dear Richard:

After you have redesigned the table, the query would need to change
somewhat:

SELECT *
FROM [Meter Usage] MU0, [Meter Usage] MU1
WHERE MU0.Date = #11/26/05#
AND MU1.Date = #11/25/05#
AND MU1.MeterID = MU0.MeterID

It may be better to construct this as an INNER JOIN as well.

I'm also wondering what you have stored in this table other than a date and
meter reading. Perhaps there are other table changes you should consider
when doing this.

Tom Ellison

Dear Tom,

I tried the query and it did work, however I am going to set up my table
like you have said so that I have done it correctly. I am going to work
on
this for a while and I will get back to you later if that is okay. Will
the
same query work for the new table if I have set the table up like you
suggested?

Richard

Dear Richard:

When it comes to being able to make an analysis like the one you mention,
it
really is much better to have 20 rows for 20 meters than just on row for
20
meters.

But the real test is this. What would you have to do to add a new meter?
If the answer is that you'd have to add a new column to a table and
change
all the forms and reports, then you have a severe normalization problem.

For your more immediate needs, did you try the query I proposed?

Tom Ellison

Dear Tom,

I'm not real sure if we are on the same page right now. I know that I
have
some normalization issues in this database, particuliarly with the
naming,
but I thought I had the tables set up correctly. We've been using the
database for about three years and it's done more than anyone expected
it
to.
I want to make sure I am understanding what you said correctly, so for
11/26/2005, I would have some 20 rows of data? The way I have
understood
it
is that I should have one record for all of the day's meter readings,
not
20.


:

Dear Richard:

Looking at your data, I had already suspected you have a large amount
of
normalization problem.

To be effective, your database should have one row for each
combination
of
meter and date. There are excellent ways of doing this.

Typically, you would have a table of all the meters involved. Each
meter
would be uniquely named.

You could temprorarily fix this by creating a UNION query that creates
this
appearance.

For the time being, then, all you comparisons are between a single
record
for all of today's readings and another record for all of yesterday's
readings. The foundational query for this would be:

SELECT *
FROM [Meter Usage] MU0, [Meter Usage] MU1
WHERE MU0.Date = #11/26/05#
AND MU1.Date = #11/25/05#

This puts all the columns of 2 of your records together. You can then
simply add a difference calculation between them.

This could become complicated by the 255 column limitation for any
table
or
query. Probably you wouldn't have much problem for now, but if you
have
more and more meters then it will become more and more of a problem.

As it is, if you needed to add a meter you'd need to add a new column
to
the
table for its data and then change all the reports and forms working
with
this. That's really the hard way of doing it.

Tom Ellison


Dear Tom,

The relationship between the rows is determined by the date field.
I
apologize if the way I pasted the information is confusing. If you
would
prefer, I could figure out an easier way to get this information to
you.
Each row is the reading for one meter on one day. If you look to
the
far
left column of the message, you will see the field names, such as:LT
24,
LT
18, LT 16 and Filter 14-23. Each one of those represents a meter
and
the
number that follows them is the meter reading for that day.

Richard

:

Dear Richard:

Looking at the two records you provided, the first question is
this:
On
which column(s) is the relationship between the rows determined?
How
can
I
tell that these two rows are for the same meter? Is each row the
reading
for one meter on one day?

Tom Ellison

Dear Tom,

I see that I forgot to give you some data. So I am reposting
it,
sorry.

Table - Meter Usage
ID # - Primary Key - 1719
Date - 8/17/05
Day - 230
Employee Name - Richard Whittet
LT 24 - 0.49
LT 18 - 1.154
LT 16 - 2.592
CW 20 - 1.477
LT Mariana - 0.244
LTCW 42 - 11.396
LT 8 - 0.195
North Carter - * 499246
Comments
12 Inch RW - * 18635057
Turnout #1 - 0.98
North Recycle - * 488734
Filter 14 - * 137059
Filter 15 - * 122433
Filter 16 - * 3282807
Filter 17 - * 2755124
Filter 18 - * 2740611
Filter 19 - * 1275411
Filter 20 - * 1028674
Filter 21 - * 1136286
Filter 22 - * 1222160
Filter 23 - * 1072176
14 Inch BW - * 7540
South Recycle - * 1318149
South Recycle Pond Switch - yes
South Recycle Pond - 1
North Recycle Pond Switch - no
North Recycle Pond - 1
Demand High - 8.450
Demand Low AM - 5.550
CB-2 Minimum Level - 3.50
CB-3 Minimum Level - 4.21
3-4 Flowrate - 2.10
5-8 Flowrate - 4.56

Table - Meter Usage
ID # - Primary Key - 1720
Date - 8/18/05
Day - 231
Employee Name - Richard Whittet
LT 24 - 0.53
LT 18 - 1.235
LT 16 - 2.844
CW 20 - 1.427
LT Mariana - 0.315
LTCW 42 - 11.926
LT 8 - 0.213
North Carter - * 500049
Comments
12 Inch RW - * 18639524
Turnout #1 - 1.915
North Recycle - * 492855
Filter 14 - * 138449
Filter 15 - * 123823
Filter 16 - * 3284193
Filter 17 - * 2756494
Filter 18 - * 2741978
Filter 19 - * 1276836
Filter 20 - * 1030078
Filter 21 - * 1137684
Filter 22 - * 1223561
Filter 23 - * 1073563
14 Inch BW - * 7829
South Recycle - * 1319259
South Recycle Pond Switch - no
South Recycle Pond - 3
North Recycle Pond Switch - no
North Recycle Pond - 1
Demand High - 8.689
Demand Low AM - 4.689
CB-2 Minimum Level - 3.75
CB-3 Minimum Level - 4.15
3-4 Flowrate - 2.05
5-8 Flowrate - 4.50
Thanks again for your assistance.
Richard





:

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.

 

[RWhittet]

Dear Tom,

All that we are storing is a date and a meter reading. There are several
calculations used just based on the readings when added as a whole or as a
group, but after talking with you I think I can just create these
calculations in my query and go from there. I have about 20 other tables in
this database that aren't normalized so I will be getting to those soon. The
issues I see arising from following all of the normalization rules is severe
in our case because of the numerous parameters we look at and record daily.
I hate to sound disappointed but I have already put so much time into this
database and it's frustrating when you find out you've been wrong all along.
I do appreciate your help in getting me on the right track.

Richard

Dear Richard:

After you have redesigned the table, the query would need to change
somewhat:

SELECT *
FROM [Meter Usage] MU0, [Meter Usage] MU1
WHERE MU0.Date = #11/26/05#
AND MU1.Date = #11/25/05#
AND MU1.MeterID = MU0.MeterID

It may be better to construct this as an INNER JOIN as well.

I'm also wondering what you have stored in this table other than a date and
meter reading. Perhaps there are other table changes you should consider
when doing this.

Tom Ellison

Dear Tom,

I tried the query and it did work, however I am going to set up my table
like you have said so that I have done it correctly. I am going to work
on
this for a while and I will get back to you later if that is okay. Will
the
same query work for the new table if I have set the table up like you
suggested?

Richard

Dear Richard:

When it comes to being able to make an analysis like the one you mention,
it
really is much better to have 20 rows for 20 meters than just on row for
20
meters.

But the real test is this. What would you have to do to add a new meter?
If the answer is that you'd have to add a new column to a table and
change
all the forms and reports, then you have a severe normalization problem.

For your more immediate needs, did you try the query I proposed?

Tom Ellison


Dear Tom,

I'm not real sure if we are on the same page right now. I know that I
have
some normalization issues in this database, particuliarly with the
naming,
but I thought I had the tables set up correctly. We've been using the
database for about three years and it's done more than anyone expected
it
to.
I want to make sure I am understanding what you said correctly, so for
11/26/2005, I would have some 20 rows of data? The way I have
understood
it
is that I should have one record for all of the day's meter readings,
not
20.


:

Dear Richard:

Looking at your data, I had already suspected you have a large amount
of
normalization problem.

To be effective, your database should have one row for each
combination
of
meter and date. There are excellent ways of doing this.

Typically, you would have a table of all the meters involved. Each
meter
would be uniquely named.

You could temprorarily fix this by creating a UNION query that creates
this
appearance.

For the time being, then, all you comparisons are between a single
record
for all of today's readings and another record for all of yesterday's
readings. The foundational query for this would be:

SELECT *
FROM [Meter Usage] MU0, [Meter Usage] MU1
WHERE MU0.Date = #11/26/05#
AND MU1.Date = #11/25/05#

This puts all the columns of 2 of your records together. You can then
simply add a difference calculation between them.

This could become complicated by the 255 column limitation for any
table
or
query. Probably you wouldn't have much problem for now, but if you
have
more and more meters then it will become more and more of a problem.

As it is, if you needed to add a meter you'd need to add a new column
to
the
table for its data and then change all the reports and forms working
with
this. That's really the hard way of doing it.

Tom Ellison


Dear Tom,

The relationship between the rows is determined by the date field.
I
apologize if the way I pasted the information is confusing. If you
would
prefer, I could figure out an easier way to get this information to
you.
Each row is the reading for one meter on one day. If you look to
the
far
left column of the message, you will see the field names, such as:LT
24,
LT
18, LT 16 and Filter 14-23. Each one of those represents a meter
and
the
number that follows them is the meter reading for that day.

Richard

:

Dear Richard:

Looking at the two records you provided, the first question is
this:
On
which column(s) is the relationship between the rows determined?
How
can
I
tell that these two rows are for the same meter? Is each row the
reading
for one meter on one day?

Tom Ellison

Dear Tom,

I see that I forgot to give you some data. So I am reposting
it,
sorry.

Table - Meter Usage
ID # - Primary Key - 1719
Date - 8/17/05
Day - 230
Employee Name - Richard Whittet
LT 24 - 0.49
LT 18 - 1.154
LT 16 - 2.592
CW 20 - 1.477
LT Mariana - 0.244
LTCW 42 - 11.396
LT 8 - 0.195
North Carter - * 499246
Comments
12 Inch RW - * 18635057
Turnout #1 - 0.98
North Recycle - * 488734
Filter 14 - * 137059
Filter 15 - * 122433
Filter 16 - * 3282807
Filter 17 - * 2755124
Filter 18 - * 2740611
Filter 19 - * 1275411
Filter 20 - * 1028674
Filter 21 - * 1136286
Filter 22 - * 1222160
Filter 23 - * 1072176
14 Inch BW - * 7540
South Recycle - * 1318149
South Recycle Pond Switch - yes
South Recycle Pond - 1
North Recycle Pond Switch - no
North Recycle Pond - 1
Demand High - 8.450
Demand Low AM - 5.550
CB-2 Minimum Level - 3.50
CB-3 Minimum Level - 4.21
3-4 Flowrate - 2.10
5-8 Flowrate - 4.56

Table - Meter Usage
ID # - Primary Key - 1720
Date - 8/18/05
Day - 231
Employee Name - Richard Whittet
LT 24 - 0.53
LT 18 - 1.235
LT 16 - 2.844
CW 20 - 1.427
LT Mariana - 0.315
LTCW 42 - 11.926
LT 8 - 0.213
North Carter - * 500049
Comments
12 Inch RW - * 18639524
Turnout #1 - 1.915
North Recycle - * 492855
Filter 14 - * 138449
Filter 15 - * 123823
Filter 16 - * 3284193
Filter 17 - * 2756494
Filter 18 - * 2741978
Filter 19 - * 1276836
Filter 20 - * 1030078
Filter 21 - * 1137684
Filter 22 - * 1223561
Filter 23 - * 1073563
14 Inch BW - * 7829
South Recycle - * 1319259
South Recycle Pond Switch - no
South Recycle Pond - 3
North Recycle Pond Switch - no
North Recycle Pond - 1
Demand High - 8.689
Demand Low AM - 4.689
CB-2 Minimum Level - 3.75
CB-3 Minimum Level - 4.15
3-4 Flowrate - 2.05
5-8 Flowrate - 4.50
Thanks again for your assistance.
Richard





:

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

 

[RWhittet]

Dear Tom,

I wanted to thank you again for assisting me with this issue. The query you
built has allowed me to calculate everything I needed to with our meters. I
am grateful that all of you who help us rookies out are willing to do so.

Sincerely,

Richard Whittet

Dear John:

Thanks for the kind words.

I've known John as a fellow MVP (which we are not at the moment) and met him
in Seattle in 2004 at the MVP Summit. He, like all the MVPs I've met, is
very good at what he does and generous with his good advise. To hear from
him like this is gratifying.

Hope to see you at one of the MVP confabs again, John! Betsy sends her
greeting, too!

Tom Ellison

Well, you are getting much more comprehensive advice from Tom Ellison. If
you
can, use his advice.

The DLookup function expects strings for its arguments, so

Expr1: DLookUp("[South Recycle]","[Meter Usage]", "[Day]=#"&
DateDiff("d",-1,[Day]) &"#")

The third argument is basically a WHERE clause without "WHERE" at the
beginning
of the clause. It should end up being a string that looks like
"[Day]=#11/12/2005#"

Where 11/12/2005 is one less than the value of Day in the current record.

Like I said work with Tom Ellison - he will help you develop a robust
solution
that will take into account the missing data.

Is there a way to manipulate this expression to where it will work in the
query? I have been trying different things but it always returns the
same
days reading. I think the problem lies in the criteria of the argument.
Also, I'm not good with the SQL language so if you could retype it using
my
field and table names that would be great.

Expr1: DLookUp([South Recycle],"Meter Usage",[Day]-1)

:

If you have one reading for every day then this is fairly simple. If
you have
multiple readings or days get skipped then this gets to be a bit more
complex.

Simplest case - one reading EVERY day. (Substitute your field and
table names)

SELECT A.MeterID, A.ReadingDate, (A.Reading - B.Reading)/1000
FROM YourTable as A INNER JOIN YourTable As B
ON A.ReadingDate = B.ReadingDate -1
AND R.MeterID = B.MeterID

This will return NO record (row) for any case where there is no
immediate prior
date. So for instance the oldest record in the table will not be in
your list.

By the way, if you can do it with DLookup on a form, you should be able
to
transfer that same logic into the query. You could post your DLookup
code and
someone may be able to suggest how you can use that to get the desired
results.


RWhittet wrote:

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

 

[Tom Ellison]

Dear Richard:

I'm glad to hear you're underway on this. If you have future difficulty,
you may certainly come back here with further questions.

Tom Ellison

Dear Tom,

I wanted to thank you again for assisting me with this issue. The query
you
built has allowed me to calculate everything I needed to with our meters.
I
am grateful that all of you who help us rookies out are willing to do so.

Sincerely,

Richard Whittet

Dear John:

Thanks for the kind words.

I've known John as a fellow MVP (which we are not at the moment) and met
him
in Seattle in 2004 at the MVP Summit. He, like all the MVPs I've met, is
very good at what he does and generous with his good advise. To hear
from
him like this is gratifying.

Hope to see you at one of the MVP confabs again, John! Betsy sends her
greeting, too!

Tom Ellison

Well, you are getting much more comprehensive advice from Tom Ellison.
If
you
can, use his advice.

The DLookup function expects strings for its arguments, so

Expr1: DLookUp("[South Recycle]","[Meter Usage]", "[Day]=#"&
DateDiff("d",-1,[Day]) &"#")

The third argument is basically a WHERE clause without "WHERE" at the
beginning
of the clause. It should end up being a string that looks like
"[Day]=#11/12/2005#"

Where 11/12/2005 is one less than the value of Day in the current
record.

Like I said work with Tom Ellison - he will help you develop a robust
solution
that will take into account the missing data.

RWhittet wrote:

Is there a way to manipulate this expression to where it will work in
the
query? I have been trying different things but it always returns the
same
days reading. I think the problem lies in the criteria of the
argument.
Also, I'm not good with the SQL language so if you could retype it
using
my
field and table names that would be great.

Expr1: DLookUp([South Recycle],"Meter Usage",[Day]-1)

:

If you have one reading for every day then this is fairly simple.
If
you have
multiple readings or days get skipped then this gets to be a bit
more
complex.

Simplest case - one reading EVERY day. (Substitute your field and
table names)

SELECT A.MeterID, A.ReadingDate, (A.Reading - B.Reading)/1000
FROM YourTable as A INNER JOIN YourTable As B
ON A.ReadingDate = B.ReadingDate -1
AND R.MeterID = B.MeterID

This will return NO record (row) for any case where there is no
immediate prior
date. So for instance the oldest record in the table will not be in
your list.

By the way, if you can do it with DLookup on a form, you should be
able
to
transfer that same logic into the query. You could post your
DLookup
code and
someone may be able to suggest how you can use that to get the
desired
results.


RWhittet wrote:

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