Calculations on multiple records

[Steve Haack]

I have a table which stores dates for a utility meter reading, and it is
related to the customer whos meter is being read.

The table has MeterReadingDate, which is the date the meter was read,
another column called MeterValue which is the vlaue that was on the meter.

In a report, I want to look at the last 2 meter readings, which I can do
with a query, but what I am not sure how to do is to calculate the difference
between the meter readings, so I know how much to charge a customer for the
utility usage. For example:

Date: Meter Reading:
__________________________________________________
1/1/06 2745
2/1/06 3267

Usage this month: 522 units

I am not sure how to do calculations across the same field from multiple
records.

 

[Allen Browne]

See:
Referring to a Field in the Previous Record or Next Record
at:
http://support.microsoft.com/kb/210504

The article suggests 2 methods. A third would be a subquery. Type something
like this into the Field row in query design:
Units: (SELECT TOP 1 [Meter Reading] FROM [Table1] AS Dupe
WHERE Dupe.[Date] < [Table1].[Date]
AND Dupe.[MeterID] = [Table1].[MeterID]
ORDER BY Dupe.[Date] DESC, Dupe.ID) - [Meter Reading]

 

[Steve Haack]

The information in that article sort of works for me. In my example below, my
query is returning only the meter readings from 2006, which is what I want.
My problem is that these records are not necessarily in sequential order in
the data base. So when I use DLookUp to locate a record the next record based
on its ID I am getting the one with the next ID, which may not be what I
wanted due to the order the data was entered into the database.

What I really want, is for it to give me the next one being returned by the
query.

Does that make sense?

Steve

See:
Referring to a Field in the Previous Record or Next Record
at:
http://support.microsoft.com/kb/210504

The article suggests 2 methods. A third would be a subquery. Type something
like this into the Field row in query design:
Units: (SELECT TOP 1 [Meter Reading] FROM [Table1] AS Dupe
WHERE Dupe.[Date] < [Table1].[Date]
AND Dupe.[MeterID] = [Table1].[MeterID]
ORDER BY Dupe.[Date] DESC, Dupe.ID) - [Meter Reading]

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

I have a table which stores dates for a utility meter reading, and it is
related to the customer whos meter is being read.

The table has MeterReadingDate, which is the date the meter was read,
another column called MeterValue which is the vlaue that was on the meter.

In a report, I want to look at the last 2 meter readings, which I can do
with a query, but what I am not sure how to do is to calculate the
difference
between the meter readings, so I know how much to charge a customer for
the
utility usage. For example:

Date: Meter Reading:
__________________________________________________
1/1/06 2745
2/1/06 3267

Usage this month: 522 units

I am not sure how to do calculations across the same field from multiple
records.

 

[Allen Browne]

Yes, DLookup() lacks any way to specify how the records should be sorted, so
it just returns the first match rather than the one you want.

There is a replacment for it here:
ELookup - an extended replacement for DLookup()
at:
http://allenbrowne.com/ser-42.html
ELookup() does let you specify what field(s) to sort by.

But my preference would still be for the subquery, which could be an order
of magnitude faster than DLookkup().

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

The information in that article sort of works for me. In my example below,
my
query is returning only the meter readings from 2006, which is what I
want.
My problem is that these records are not necessarily in sequential order
in
the data base. So when I use DLookUp to locate a record the next record
based
on its ID I am getting the one with the next ID, which may not be what I
wanted due to the order the data was entered into the database.

What I really want, is for it to give me the next one being returned by
the
query.

Does that make sense?

Steve

See:
Referring to a Field in the Previous Record or Next Record
at:
http://support.microsoft.com/kb/210504

The article suggests 2 methods. A third would be a subquery. Type
something
like this into the Field row in query design:
Units: (SELECT TOP 1 [Meter Reading] FROM [Table1] AS Dupe
WHERE Dupe.[Date] < [Table1].[Date]
AND Dupe.[MeterID] = [Table1].[MeterID]
ORDER BY Dupe.[Date] DESC, Dupe.ID) - [Meter Reading]

I have a table which stores dates for a utility meter reading, and it is
related to the customer whos meter is being read.

The table has MeterReadingDate, which is the date the meter was read,
another column called MeterValue which is the vlaue that was on the
meter.

In a report, I want to look at the last 2 meter readings, which I can
do
with a query, but what I am not sure how to do is to calculate the
difference
between the meter readings, so I know how much to charge a customer for
the
utility usage. For example:

Date: Meter Reading:
__________________________________________________
1/1/06 2745
2/1/06 3267

Usage this month: 522 units

I am not sure how to do calculations across the same field from
multiple
records.

 

[Steve Haack]

OK. This is all new to me, but I will give thesubquery a try and see what
happens. Thanks for the quick responses.

Yes, DLookup() lacks any way to specify how the records should be sorted, so
it just returns the first match rather than the one you want.

There is a replacment for it here:
ELookup - an extended replacement for DLookup()
at:
http://allenbrowne.com/ser-42.html
ELookup() does let you specify what field(s) to sort by.

But my preference would still be for the subquery, which could be an order
of magnitude faster than DLookkup().

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

The information in that article sort of works for me. In my example below,
my
query is returning only the meter readings from 2006, which is what I
want.
My problem is that these records are not necessarily in sequential order
in
the data base. So when I use DLookUp to locate a record the next record
based
on its ID I am getting the one with the next ID, which may not be what I
wanted due to the order the data was entered into the database.

What I really want, is for it to give me the next one being returned by
the
query.

Does that make sense?

Steve

See:
Referring to a Field in the Previous Record or Next Record
at:
http://support.microsoft.com/kb/210504

The article suggests 2 methods. A third would be a subquery. Type
something
like this into the Field row in query design:
Units: (SELECT TOP 1 [Meter Reading] FROM [Table1] AS Dupe
WHERE Dupe.[Date] < [Table1].[Date]
AND Dupe.[MeterID] = [Table1].[MeterID]
ORDER BY Dupe.[Date] DESC, Dupe.ID) - [Meter Reading]

I have a table which stores dates for a utility meter reading, and it is
related to the customer whos meter is being read.

The table has MeterReadingDate, which is the date the meter was read,
another column called MeterValue which is the vlaue that was on the
meter.

In a report, I want to look at the last 2 meter readings, which I can
do
with a query, but what I am not sure how to do is to calculate the
difference
between the meter readings, so I know how much to charge a customer for
the
utility usage. For example:

Date: Meter Reading:
__________________________________________________
1/1/06 2745
2/1/06 3267

Usage this month: 522 units

I am not sure how to do calculations across the same field from
multiple
records.

 

[Steve Haack]

I tried the sub query and couldn't seem to get it to work, but I will try
again.

I also tried the code that is in the KB article that you referenced, and it
works fine on a Form, but I could not get it to work on a Report. I changed
the data type to be a Report and not a Form as it originally had, and I also
change the RecordsetClone to just RecordSet since the clone function is not
supported for the Report object, but I was still not able to get it to work.
Can you tell me what I need to do to make that code work in a report?

Steve

OK. This is all new to me, but I will give thesubquery a try and see what
happens. Thanks for the quick responses.

Yes, DLookup() lacks any way to specify how the records should be sorted, so
it just returns the first match rather than the one you want.

There is a replacment for it here:
ELookup - an extended replacement for DLookup()
at:
http://allenbrowne.com/ser-42.html
ELookup() does let you specify what field(s) to sort by.

But my preference would still be for the subquery, which could be an order
of magnitude faster than DLookkup().

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

The information in that article sort of works for me. In my example below,
my
query is returning only the meter readings from 2006, which is what I
want.
My problem is that these records are not necessarily in sequential order
in
the data base. So when I use DLookUp to locate a record the next record
based
on its ID I am getting the one with the next ID, which may not be what I
wanted due to the order the data was entered into the database.

What I really want, is for it to give me the next one being returned by
the
query.

Does that make sense?

Steve

:

See:
Referring to a Field in the Previous Record or Next Record
at:
http://support.microsoft.com/kb/210504

The article suggests 2 methods. A third would be a subquery. Type
something
like this into the Field row in query design:
Units: (SELECT TOP 1 [Meter Reading] FROM [Table1] AS Dupe
WHERE Dupe.[Date] < [Table1].[Date]
AND Dupe.[MeterID] = [Table1].[MeterID]
ORDER BY Dupe.[Date] DESC, Dupe.ID) - [Meter Reading]

I have a table which stores dates for a utility meter reading, and it is
related to the customer whos meter is being read.

The table has MeterReadingDate, which is the date the meter was read,
another column called MeterValue which is the vlaue that was on the
meter.

In a report, I want to look at the last 2 meter readings, which I can
do
with a query, but what I am not sure how to do is to calculate the
difference
between the meter readings, so I know how much to charge a customer for
the
utility usage. For example:

Date: Meter Reading:
__________________________________________________
1/1/06 2745
2/1/06 3267

Usage this month: 522 units

I am not sure how to do calculations across the same field from
multiple
records.

 

[Allen Browne]

Method 2 in the kb article won't work for reports.

It is possible to work with the report's events, but doing it in the query
is more reliable.

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

I tried the sub query and couldn't seem to get it to work, but I will try
again.

I also tried the code that is in the KB article that you referenced, and
it
works fine on a Form, but I could not get it to work on a Report. I
changed
the data type to be a Report and not a Form as it originally had, and I
also
change the RecordsetClone to just RecordSet since the clone function is
not
supported for the Report object, but I was still not able to get it to
work.
Can you tell me what I need to do to make that code work in a report?

Steve

OK. This is all new to me, but I will give thesubquery a try and see what
happens. Thanks for the quick responses.

Yes, DLookup() lacks any way to specify how the records should be
sorted, so
it just returns the first match rather than the one you want.

There is a replacment for it here:
ELookup - an extended replacement for DLookup()
at:
http://allenbrowne.com/ser-42.html
ELookup() does let you specify what field(s) to sort by.

But my preference would still be for the subquery, which could be an
order
of magnitude faster than DLookkup().

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

The information in that article sort of works for me. In my example
below,
my
query is returning only the meter readings from 2006, which is what I
want.
My problem is that these records are not necessarily in sequential
order
in
the data base. So when I use DLookUp to locate a record the next
record
based
on its ID I am getting the one with the next ID, which may not be
what I
wanted due to the order the data was entered into the database.

What I really want, is for it to give me the next one being returned
by
the
query.

Does that make sense?

Steve

:

See:
Referring to a Field in the Previous Record or Next Record
at:
http://support.microsoft.com/kb/210504

The article suggests 2 methods. A third would be a subquery. Type
something
like this into the Field row in query design:
Units: (SELECT TOP 1 [Meter Reading] FROM [Table1] AS Dupe
WHERE Dupe.[Date] < [Table1].[Date]
AND Dupe.[MeterID] = [Table1].[MeterID]
ORDER BY Dupe.[Date] DESC, Dupe.ID) - [Meter Reading]

message
I have a table which stores dates for a utility meter reading, and
it is
related to the customer whos meter is being read.

The table has MeterReadingDate, which is the date the meter was
read,
another column called MeterValue which is the vlaue that was on
the
meter.

In a report, I want to look at the last 2 meter readings, which I
can
do
with a query, but what I am not sure how to do is to calculate the
difference
between the meter readings, so I know how much to charge a
customer for
the
utility usage. For example:

Date: Meter Reading:
__________________________________________________
1/1/06 2745
2/1/06 3267

Usage this month: 522 units

I am not sure how to do calculations across the same field from
multiple
records.

 

[Steve Haack]

Bummer. Well, I can't seem to get the subquery thing to work, but thanks for
the help anyway.

Method 2 in the kb article won't work for reports.

It is possible to work with the report's events, but doing it in the query
is more reliable.

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

I tried the sub query and couldn't seem to get it to work, but I will try
again.

I also tried the code that is in the KB article that you referenced, and
it
works fine on a Form, but I could not get it to work on a Report. I
changed
the data type to be a Report and not a Form as it originally had, and I
also
change the RecordsetClone to just RecordSet since the clone function is
not
supported for the Report object, but I was still not able to get it to
work.
Can you tell me what I need to do to make that code work in a report?

Steve

OK. This is all new to me, but I will give thesubquery a try and see what
happens. Thanks for the quick responses.

:

Yes, DLookup() lacks any way to specify how the records should be
sorted, so
it just returns the first match rather than the one you want.

There is a replacment for it here:
ELookup - an extended replacement for DLookup()
at:
http://allenbrowne.com/ser-42.html
ELookup() does let you specify what field(s) to sort by.

But my preference would still be for the subquery, which could be an
order
of magnitude faster than DLookkup().

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

The information in that article sort of works for me. In my example
below,
my
query is returning only the meter readings from 2006, which is what I
want.
My problem is that these records are not necessarily in sequential
order
in
the data base. So when I use DLookUp to locate a record the next
record
based
on its ID I am getting the one with the next ID, which may not be
what I
wanted due to the order the data was entered into the database.

What I really want, is for it to give me the next one being returned
by
the
query.

Does that make sense?

Steve

:

See:
Referring to a Field in the Previous Record or Next Record
at:
http://support.microsoft.com/kb/210504

The article suggests 2 methods. A third would be a subquery. Type
something
like this into the Field row in query design:
Units: (SELECT TOP 1 [Meter Reading] FROM [Table1] AS Dupe
WHERE Dupe.[Date] < [Table1].[Date]
AND Dupe.[MeterID] = [Table1].[MeterID]
ORDER BY Dupe.[Date] DESC, Dupe.ID) - [Meter Reading]

message
I have a table which stores dates for a utility meter reading, and
it is
related to the customer whos meter is being read.

The table has MeterReadingDate, which is the date the meter was
read,
another column called MeterValue which is the vlaue that was on
the
meter.

In a report, I want to look at the last 2 meter readings, which I
can
do
with a query, but what I am not sure how to do is to calculate the
difference
between the meter readings, so I know how much to charge a
customer for
the
utility usage. For example:

Date: Meter Reading:
__________________________________________________
1/1/06 2745
2/1/06 3267

Usage this month: 522 units

I am not sure how to do calculations across the same field from
multiple
records.

 

[Al Camp]

Steve,
Just a shot in the dark...
**IF** you always take the reading on the same day of the month (1/1/06 vs 2/1/06 seems
to suggest so) then you might be able to create a pass thru (P/T)query that subtracts 1
month from each reading date, and then relinks back to the original table via the date.
So your query would produce this...
RealDate: Meter Reading: P/TDate: P/TMeterRead Diff
1/1/06 2745 1/1/06 3267 522
2/1/06 3267 2/1/06 4000 733

--
hth
Al Camp
Candia Computer Consulting - Candia NH
http://home.comcast.net/~cccsolutions

Bummer. Well, I can't seem to get the subquery thing to work, but thanks for
the help anyway.

Method 2 in the kb article won't work for reports.

It is possible to work with the report's events, but doing it in the query
is more reliable.

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

I tried the sub query and couldn't seem to get it to work, but I will try
again.

I also tried the code that is in the KB article that you referenced, and
it
works fine on a Form, but I could not get it to work on a Report. I
changed
the data type to be a Report and not a Form as it originally had, and I
also
change the RecordsetClone to just RecordSet since the clone function is
not
supported for the Report object, but I was still not able to get it to
work.
Can you tell me what I need to do to make that code work in a report?

Steve

:

OK. This is all new to me, but I will give thesubquery a try and see what
happens. Thanks for the quick responses.

:

Yes, DLookup() lacks any way to specify how the records should be
sorted, so
it just returns the first match rather than the one you want.

There is a replacment for it here:
ELookup - an extended replacement for DLookup()
at:
http://allenbrowne.com/ser-42.html
ELookup() does let you specify what field(s) to sort by.

But my preference would still be for the subquery, which could be an
order
of magnitude faster than DLookkup().

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

The information in that article sort of works for me. In my example
below,
my
query is returning only the meter readings from 2006, which is what I
want.
My problem is that these records are not necessarily in sequential
order
in
the data base. So when I use DLookUp to locate a record the next
record
based
on its ID I am getting the one with the next ID, which may not be
what I
wanted due to the order the data was entered into the database.

What I really want, is for it to give me the next one being returned
by
the
query.

Does that make sense?

Steve

:

See:
Referring to a Field in the Previous Record or Next Record
at:
http://support.microsoft.com/kb/210504

The article suggests 2 methods. A third would be a subquery. Type
something
like this into the Field row in query design:
Units: (SELECT TOP 1 [Meter Reading] FROM [Table1] AS Dupe
WHERE Dupe.[Date] < [Table1].[Date]
AND Dupe.[MeterID] = [Table1].[MeterID]
ORDER BY Dupe.[Date] DESC, Dupe.ID) - [Meter Reading]

message
I have a table which stores dates for a utility meter reading, and
it is
related to the customer whos meter is being read.

The table has MeterReadingDate, which is the date the meter was
read,
another column called MeterValue which is the vlaue that was on
the
meter.

In a report, I want to look at the last 2 meter readings, which I
can
do
with a query, but what I am not sure how to do is to calculate the
difference
between the meter readings, so I know how much to charge a
customer for
the
utility usage. For example:

Date: Meter Reading:
__________________________________________________
1/1/06 2745
2/1/06 3267

Usage this month: 522 units

I am not sure how to do calculations across the same field from
multiple
records.

 

[Steve Haack]

Actually, we only do a reading 3 time per summer (this is for a summer
resort), and it isn't always at the same day of the month.

Maybe I am just missing something here. All I want to do is perform a query
to get the readings for a given year for a specific lot (which I can easily
do), and then I want to be able to iterate through the records in that query
and examine each instance of a given field. Is that really so hard to do?

Steve,
Just a shot in the dark...
**IF** you always take the reading on the same day of the month (1/1/06 vs 2/1/06 seems
to suggest so) then you might be able to create a pass thru (P/T)query that subtracts 1
month from each reading date, and then relinks back to the original table via the date.
So your query would produce this...
RealDate: Meter Reading: P/TDate: P/TMeterRead Diff
1/1/06 2745 1/1/06 3267 522
2/1/06 3267 2/1/06 4000 733

--
hth
Al Camp
Candia Computer Consulting - Candia NH
http://home.comcast.net/~cccsolutions

Bummer. Well, I can't seem to get the subquery thing to work, but thanks for
the help anyway.

Method 2 in the kb article won't work for reports.

It is possible to work with the report's events, but doing it in the query
is more reliable.

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

I tried the sub query and couldn't seem to get it to work, but I will try
again.

I also tried the code that is in the KB article that you referenced, and
it
works fine on a Form, but I could not get it to work on a Report. I
changed
the data type to be a Report and not a Form as it originally had, and I
also
change the RecordsetClone to just RecordSet since the clone function is
not
supported for the Report object, but I was still not able to get it to
work.
Can you tell me what I need to do to make that code work in a report?

Steve

:

OK. This is all new to me, but I will give thesubquery a try and see what
happens. Thanks for the quick responses.

:

Yes, DLookup() lacks any way to specify how the records should be
sorted, so
it just returns the first match rather than the one you want.

There is a replacment for it here:
ELookup - an extended replacement for DLookup()
at:
http://allenbrowne.com/ser-42.html
ELookup() does let you specify what field(s) to sort by.

But my preference would still be for the subquery, which could be an
order
of magnitude faster than DLookkup().

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

The information in that article sort of works for me. In my example
below,
my
query is returning only the meter readings from 2006, which is what I
want.
My problem is that these records are not necessarily in sequential
order
in
the data base. So when I use DLookUp to locate a record the next
record
based
on its ID I am getting the one with the next ID, which may not be
what I
wanted due to the order the data was entered into the database.

What I really want, is for it to give me the next one being returned
by
the
query.

Does that make sense?

Steve

:

See:
Referring to a Field in the Previous Record or Next Record
at:
http://support.microsoft.com/kb/210504

The article suggests 2 methods. A third would be a subquery. Type
something
like this into the Field row in query design:
Units: (SELECT TOP 1 [Meter Reading] FROM [Table1] AS Dupe
WHERE Dupe.[Date] < [Table1].[Date]
AND Dupe.[MeterID] = [Table1].[MeterID]
ORDER BY Dupe.[Date] DESC, Dupe.ID) - [Meter Reading]

message
I have a table which stores dates for a utility meter reading, and
it is
related to the customer whos meter is being read.

The table has MeterReadingDate, which is the date the meter was
read,
another column called MeterValue which is the vlaue that was on
the
meter.

In a report, I want to look at the last 2 meter readings, which I
can
do
with a query, but what I am not sure how to do is to calculate the
difference
between the meter readings, so I know how much to charge a
customer for
the
utility usage. For example:

Date: Meter Reading:
__________________________________________________
1/1/06 2745
2/1/06 3267

Usage this month: 522 units

I am not sure how to do calculations across the same field from
multiple
records.

 

[Al Camp]

Steve,
It's not quite as simple as adding/averaging/ a column, over all the values, since you
need the difference from month to month, and a sum for the season.
Given your first post...
Date: Meter Reading:
__________________________________________________
1/1/06 2745
2/1/06 3267
Usage this month: 522 units

The 2/1/06 record doesn't know that the last reading was 2745, so it doesn't really know
that 3267-2745 = 522
And since the readings are not taken on a consistent schedule, there's no "reliable" logic
to hang code on.

Here's what I'd do with a different table design for the readings.

MainForm Lots
LotNo All the Lot information
12

Subform Readings
LotNo Date BeginReading EndReading Difference
12 1/01/06 250 500 250
12 1/10/06 500 625 125
12 1/25/06 625 725 100
12 2/7/06 725 850 125
--------
500

You could make the BeginReading default to the last value (max by date) of EndReading for
that Lot, so in effect, you would still be only entering an EndReading each week or so.
This setup will make it a breeze to create a report filtered by year for each lot, with
the seasonal totals.
--
hth
Al Camp
Candia Computer Consulting - Candia NH
http://home.comcast.net/~cccsolutions

Actually, we only do a reading 3 time per summer (this is for a summer
resort), and it isn't always at the same day of the month.

Maybe I am just missing something here. All I want to do is perform a query
to get the readings for a given year for a specific lot (which I can easily
do), and then I want to be able to iterate through the records in that query
and examine each instance of a given field. Is that really so hard to do?

Steve,
Just a shot in the dark...
**IF** you always take the reading on the same day of the month (1/1/06 vs 2/1/06
seems
to suggest so) then you might be able to create a pass thru (P/T)query that subtracts 1
month from each reading date, and then relinks back to the original table via the date.
So your query would produce this...
RealDate: Meter Reading: P/TDate: P/TMeterRead Diff
1/1/06 2745 1/1/06 3267 522
2/1/06 3267 2/1/06 4000 733

--
hth
Al Camp
Candia Computer Consulting - Candia NH
http://home.comcast.net/~cccsolutions

Bummer. Well, I can't seem to get the subquery thing to work, but thanks for
the help anyway.

:

Method 2 in the kb article won't work for reports.

It is possible to work with the report's events, but doing it in the query
is more reliable.

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

I tried the sub query and couldn't seem to get it to work, but I will try
again.

I also tried the code that is in the KB article that you referenced, and
it
works fine on a Form, but I could not get it to work on a Report. I
changed
the data type to be a Report and not a Form as it originally had, and I
also
change the RecordsetClone to just RecordSet since the clone function is
not
supported for the Report object, but I was still not able to get it to
work.
Can you tell me what I need to do to make that code work in a report?

Steve

:

OK. This is all new to me, but I will give thesubquery a try and see what
happens. Thanks for the quick responses.

:

Yes, DLookup() lacks any way to specify how the records should be
sorted, so
it just returns the first match rather than the one you want.

There is a replacment for it here:
ELookup - an extended replacement for DLookup()
at:
http://allenbrowne.com/ser-42.html
ELookup() does let you specify what field(s) to sort by.

But my preference would still be for the subquery, which could be an
order
of magnitude faster than DLookkup().

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

The information in that article sort of works for me. In my example
below,
my
query is returning only the meter readings from 2006, which is what I
want.
My problem is that these records are not necessarily in sequential
order
in
the data base. So when I use DLookUp to locate a record the next
record
based
on its ID I am getting the one with the next ID, which may not be
what I
wanted due to the order the data was entered into the database.

What I really want, is for it to give me the next one being returned
by
the
query.

Does that make sense?

Steve

:

See:
Referring to a Field in the Previous Record or Next Record
at:
http://support.microsoft.com/kb/210504

The article suggests 2 methods. A third would be a subquery. Type
something
like this into the Field row in query design:
Units: (SELECT TOP 1 [Meter Reading] FROM [Table1] AS Dupe
WHERE Dupe.[Date] < [Table1].[Date]
AND Dupe.[MeterID] = [Table1].[MeterID]
ORDER BY Dupe.[Date] DESC, Dupe.ID) - [Meter Reading]

message
I have a table which stores dates for a utility meter reading, and
it is
related to the customer whos meter is being read.

The table has MeterReadingDate, which is the date the meter was
read,
another column called MeterValue which is the vlaue that was on
the
meter.

In a report, I want to look at the last 2 meter readings, which I
can
do
with a query, but what I am not sure how to do is to calculate the
difference
between the meter readings, so I know how much to charge a
customer for
the
utility usage. For example:

Date: Meter Reading:
__________________________________________________
1/1/06 2745
2/1/06 3267

Usage this month: 522 units

I am not sure how to do calculations across the same field from
multiple
records.