Previous Value

  • Thread starter Thread starter Simon Harris
  • Start date Start date
S

Simon Harris

Hi All,

I should start by saying that I have kind of covered this in a previous
post, but it got lost in the thread a little, so thought I would re post,
clarifying the question.

I have a table that stores gas and electricity readings for a Caravan park.

I need to write a query that will display the previous readings for both gas
and electric. (Storing idAllocation, Gas_Reading, Electric_Reading,
Reading_Date

So, the output should be:
idAllocation | Reading_Date | Gas_Reading | Electric_Reading |
Previous_Gas_Reading | Previous_Electric_Reading

I'm really struggling with this, have been trying to use dlookup to get the
previous readings from a seperate query, but how should the query be
structured?

Can anyone offer any advice on this, would be much appreciated!

Thanks,
Simon.



--
-
* Please reply to group for the benefit of all
* Found the answer to your own question? Post it!
* Get a useful reply to one of your posts?...post an answer to another one
* Search first, post later : http://www.google.co.uk/groups
* Want my email address? Ask me in a post...Cos2MuchSpamMakesUFat!
 
Assuming your IdAllocation field is an autonumber, this auto work for you:

SELECT tblReadings.GasReading, tblReadings.ElectricReading,
tblReadings.Reading_Date, (SELECT GasReading FROM tblReadings As tblPrev
WHERE tblPrev.idAllocation =
DMax("IdAllocation","tblReadings","[IdAllocation] < " &
tblReadings.[IdAllocation])) AS PreviousGasReading, (SELECT ElectricReading
FROM tblReadings As tblPrev WHERE tblPrev.idAllocation =
DMax("IdAllocation","tblReadings","[IdAllocation] < " &
tblReadings.[IdAllocation])) AS PreviousElectricReading
FROM tblReadings;

HTH,
Barry
 
Thanks Barry for your post and the code.

When I run the query, I get the following error twice, before seeing the
expected column names, but only one row, with each columns value set to
#Name?
"At most one record can be returned by this sub query"

Any ideas?


Barry Gilbert said:
Assuming your IdAllocation field is an autonumber, this auto work for you:

SELECT tblReadings.GasReading, tblReadings.ElectricReading,
tblReadings.Reading_Date, (SELECT GasReading FROM tblReadings As tblPrev
WHERE tblPrev.idAllocation =
DMax("IdAllocation","tblReadings","[IdAllocation] < " &
tblReadings.[IdAllocation])) AS PreviousGasReading, (SELECT
ElectricReading
FROM tblReadings As tblPrev WHERE tblPrev.idAllocation =
DMax("IdAllocation","tblReadings","[IdAllocation] < " &
tblReadings.[IdAllocation])) AS PreviousElectricReading
FROM tblReadings;

HTH,
Barry

Simon Harris said:
Hi All,

I should start by saying that I have kind of covered this in a previous
post, but it got lost in the thread a little, so thought I would re post,
clarifying the question.

I have a table that stores gas and electricity readings for a Caravan
park.

I need to write a query that will display the previous readings for both
gas
and electric. (Storing idAllocation, Gas_Reading, Electric_Reading,
Reading_Date

So, the output should be:
idAllocation | Reading_Date | Gas_Reading | Electric_Reading |
Previous_Gas_Reading | Previous_Electric_Reading

I'm really struggling with this, have been trying to use dlookup to get
the
previous readings from a seperate query, but how should the query be
structured?

Can anyone offer any advice on this, would be much appreciated!

Thanks,
Simon.



--
-
* Please reply to group for the benefit of all
* Found the answer to your own question? Post it!
* Get a useful reply to one of your posts?...post an answer to another
one
* Search first, post later : http://www.google.co.uk/groups
* Want my email address? Ask me in a post...Cos2MuchSpamMakesUFat!
Click to expand...
Click to expand...
 
My example supposes that IdAllocation is an autonumber column. If it's not,
this won't work.

Barry

Simon Harris said:
Thanks Barry for your post and the code.

When I run the query, I get the following error twice, before seeing the
expected column names, but only one row, with each columns value set to
#Name?
"At most one record can be returned by this sub query"

Any ideas?


Barry Gilbert said:
Assuming your IdAllocation field is an autonumber, this auto work for you:

SELECT tblReadings.GasReading, tblReadings.ElectricReading,
tblReadings.Reading_Date, (SELECT GasReading FROM tblReadings As tblPrev
WHERE tblPrev.idAllocation =
DMax("IdAllocation","tblReadings","[IdAllocation] < " &
tblReadings.[IdAllocation])) AS PreviousGasReading, (SELECT
ElectricReading
FROM tblReadings As tblPrev WHERE tblPrev.idAllocation =
DMax("IdAllocation","tblReadings","[IdAllocation] < " &
tblReadings.[IdAllocation])) AS PreviousElectricReading
FROM tblReadings;

HTH,
Barry

Simon Harris said:
Hi All,

I should start by saying that I have kind of covered this in a previous
post, but it got lost in the thread a little, so thought I would re post,
clarifying the question.

I have a table that stores gas and electricity readings for a Caravan
park.

I need to write a query that will display the previous readings for both
gas
and electric. (Storing idAllocation, Gas_Reading, Electric_Reading,
Reading_Date

So, the output should be:
idAllocation | Reading_Date | Gas_Reading | Electric_Reading |
Previous_Gas_Reading | Previous_Electric_Reading

I'm really struggling with this, have been trying to use dlookup to get
the
previous readings from a seperate query, but how should the query be
structured?

Can anyone offer any advice on this, would be much appreciated!

Thanks,
Simon.



--
-
* Please reply to group for the benefit of all
* Found the answer to your own question? Post it!
* Get a useful reply to one of your posts?...post an answer to another
one
* Search first, post later : http://www.google.co.uk/groups
* Want my email address? Ask me in a post...Cos2MuchSpamMakesUFat!
Click to expand...
Click to expand...
Click to expand...
 
Simon,

Assuming that the table is named tblReadings, try the following SQL.

SELECT tblReadings.idAllocation, tblReadings.Reading_Date AS CurrentDate,
tblReadings.Gas_Reading AS CurrentGasReading, tblReadings.Electric_Reading AS
CurrentElectricReading, tblReadings_1.Reading_Date AS PreviousDate,
tblReadings_1.Gas_Reading AS PrevioiusGasReading,
tblReadings_1.Electric_Reading AS PrevioiusElectricReading,
[tblReadings].[Gas_Reading]-[tblReadings_1].[Gas_Reading] AS GasUsage,
[tblReadings].[Electric_Reading]-[tblReadings_1].[Electric_Reading] AS
ElectricUsage
FROM tblReadings, tblReadings AS tblReadings_1
WHERE (((tblReadings_1.Reading_Date)=(SELECT
Max([tblReadings_2].[Reading_Date]) FROM tblReadings AS tblReadings_2 WHERE
[tblReadings_2].[Reading_Date] < tblReadings.[Reading_Date])));

-Michael
 
Thanks Barry for your post and the code.

When I run the query, I get the following error twice, before seeing the
expected column names, but only one row, with each columns value set to
#Name?
"At most one record can be returned by this sub query"

Any ideas?


Barry Gilbert said:
Assuming your IdAllocation field is an autonumber, this auto work for you:

SELECT tblReadings.GasReading, tblReadings.ElectricReading,
tblReadings.Reading_Date, (SELECT GasReading FROM tblReadings As tblPrev
WHERE tblPrev.idAllocation =
DMax("IdAllocation","tblReadings","[IdAllocation] < " &
tblReadings.[IdAllocation])) AS PreviousGasReading, (SELECT
ElectricReading
FROM tblReadings As tblPrev WHERE tblPrev.idAllocation =
DMax("IdAllocation","tblReadings","[IdAllocation] < " &
tblReadings.[IdAllocation])) AS PreviousElectricReading
FROM tblReadings;

HTH,
Barry

Simon Harris said:
Hi All,

I should start by saying that I have kind of covered this in a previous
post, but it got lost in the thread a little, so thought I would re post,
clarifying the question.

I have a table that stores gas and electricity readings for a Caravan
park.

I need to write a query that will display the previous readings for both
gas
and electric. (Storing idAllocation, Gas_Reading, Electric_Reading,
Reading_Date

So, the output should be:
idAllocation | Reading_Date | Gas_Reading | Electric_Reading |
Previous_Gas_Reading | Previous_Electric_Reading

I'm really struggling with this, have been trying to use dlookup to get
the
previous readings from a seperate query, but how should the query be
structured?

Can anyone offer any advice on this, would be much appreciated!

Thanks,
Simon.



--
-
* Please reply to group for the benefit of all
* Found the answer to your own question? Post it!
* Get a useful reply to one of your posts?...post an answer to another
one
* Search first, post later : http://www.google.co.uk/groups
* Want my email address? Ask me in a post...Cos2MuchSpamMakesUFat!
Click to expand...
Click to expand...



--
 
Hi Barry,

Thanks again for your reply - idAllocation is an autonumber column. If you
have any ideas as to why this isnt working for me I'd appreciate it if you
would post them.

Regards,
Simon.

Barry Gilbert said:
My example supposes that IdAllocation is an autonumber column. If it's
not,
this won't work.

Barry

Simon Harris said:
Thanks Barry for your post and the code.

When I run the query, I get the following error twice, before seeing the
expected column names, but only one row, with each columns value set to
#Name?
"At most one record can be returned by this sub query"

Any ideas?


Barry Gilbert said:
Assuming your IdAllocation field is an autonumber, this auto work for
you:

SELECT tblReadings.GasReading, tblReadings.ElectricReading,
tblReadings.Reading_Date, (SELECT GasReading FROM tblReadings As
tblPrev
WHERE tblPrev.idAllocation =
DMax("IdAllocation","tblReadings","[IdAllocation] < " &
tblReadings.[IdAllocation])) AS PreviousGasReading, (SELECT
ElectricReading
FROM tblReadings As tblPrev WHERE tblPrev.idAllocation =
DMax("IdAllocation","tblReadings","[IdAllocation] < " &
tblReadings.[IdAllocation])) AS PreviousElectricReading
FROM tblReadings;

HTH,
Barry

:

Hi All,

I should start by saying that I have kind of covered this in a
previous
post, but it got lost in the thread a little, so thought I would re
post,
clarifying the question.

I have a table that stores gas and electricity readings for a Caravan
park.

I need to write a query that will display the previous readings for
both
gas
and electric. (Storing idAllocation, Gas_Reading, Electric_Reading,
Reading_Date

So, the output should be:
idAllocation | Reading_Date | Gas_Reading | Electric_Reading |
Previous_Gas_Reading | Previous_Electric_Reading

I'm really struggling with this, have been trying to use dlookup to
get
the
previous readings from a seperate query, but how should the query be
structured?

Can anyone offer any advice on this, would be much appreciated!

Thanks,
Simon.



--
-
* Please reply to group for the benefit of all
* Found the answer to your own question? Post it!
* Get a useful reply to one of your posts?...post an answer to another
one
* Search first, post later : http://www.google.co.uk/groups
* Want my email address? Ask me in a post...Cos2MuchSpamMakesUFat!
Click to expand...
Click to expand...
Click to expand...
 
Hi Barry,

Thanks again for your reply - idAllocation is an autonumber column. If you
have any ideas as to why this isnt working for me I'd appreciate it if you
would post them.

Regards,
Simon.

Barry Gilbert said:
My example supposes that IdAllocation is an autonumber column. If it's
not,
this won't work.

Barry

Simon Harris said:
Thanks Barry for your post and the code.

When I run the query, I get the following error twice, before seeing the
expected column names, but only one row, with each columns value set to
#Name?
"At most one record can be returned by this sub query"

Any ideas?


Barry Gilbert said:
Assuming your IdAllocation field is an autonumber, this auto work for
you:

SELECT tblReadings.GasReading, tblReadings.ElectricReading,
tblReadings.Reading_Date, (SELECT GasReading FROM tblReadings As
tblPrev
WHERE tblPrev.idAllocation =
DMax("IdAllocation","tblReadings","[IdAllocation] < " &
tblReadings.[IdAllocation])) AS PreviousGasReading, (SELECT
ElectricReading
FROM tblReadings As tblPrev WHERE tblPrev.idAllocation =
DMax("IdAllocation","tblReadings","[IdAllocation] < " &
tblReadings.[IdAllocation])) AS PreviousElectricReading
FROM tblReadings;

HTH,
Barry

:

Hi All,

I should start by saying that I have kind of covered this in a
previous
post, but it got lost in the thread a little, so thought I would re
post,
clarifying the question.

I have a table that stores gas and electricity readings for a Caravan
park.

I need to write a query that will display the previous readings for
both
gas
and electric. (Storing idAllocation, Gas_Reading, Electric_Reading,
Reading_Date

So, the output should be:
idAllocation | Reading_Date | Gas_Reading | Electric_Reading |
Previous_Gas_Reading | Previous_Electric_Reading

I'm really struggling with this, have been trying to use dlookup to
get
the
previous readings from a seperate query, but how should the query be
structured?

Can anyone offer any advice on this, would be much appreciated!

Thanks,
Simon.



--
-
* Please reply to group for the benefit of all
* Found the answer to your own question? Post it!
* Get a useful reply to one of your posts?...post an answer to another
one
* Search first, post later : http://www.google.co.uk/groups
* Want my email address? Ask me in a post...Cos2MuchSpamMakesUFat!
Click to expand...
Click to expand...
Click to expand...



--
 
Wow, thanks - I think this will work a treat! Although it does ask me to
enter a parameter value (tbl_readings.idAllocation) when I run the query.
Odd, because tbl_readings exists, which has a column named
idAllocation....Any ideas?

Thank you very much!! :)

Michael H said:
Simon,

Assuming that the table is named tblReadings, try the following SQL.

SELECT tblReadings.idAllocation, tblReadings.Reading_Date AS CurrentDate,
tblReadings.Gas_Reading AS CurrentGasReading, tblReadings.Electric_Reading
AS
CurrentElectricReading, tblReadings_1.Reading_Date AS PreviousDate,
tblReadings_1.Gas_Reading AS PrevioiusGasReading,
tblReadings_1.Electric_Reading AS PrevioiusElectricReading,
[tblReadings].[Gas_Reading]-[tblReadings_1].[Gas_Reading] AS GasUsage,
[tblReadings].[Electric_Reading]-[tblReadings_1].[Electric_Reading] AS
ElectricUsage
FROM tblReadings, tblReadings AS tblReadings_1
WHERE (((tblReadings_1.Reading_Date)=(SELECT
Max([tblReadings_2].[Reading_Date]) FROM tblReadings AS tblReadings_2
WHERE
[tblReadings_2].[Reading_Date] < tblReadings.[Reading_Date])));

-Michael

Simon Harris said:
Hi All,

I should start by saying that I have kind of covered this in a previous
post, but it got lost in the thread a little, so thought I would re post,
clarifying the question.

I have a table that stores gas and electricity readings for a Caravan
park.

I need to write a query that will display the previous readings for both
gas
and electric. (Storing idAllocation, Gas_Reading, Electric_Reading,
Reading_Date

So, the output should be:
idAllocation | Reading_Date | Gas_Reading | Electric_Reading |
Previous_Gas_Reading | Previous_Electric_Reading

I'm really struggling with this, have been trying to use dlookup to get
the
previous readings from a seperate query, but how should the query be
structured?

Can anyone offer any advice on this, would be much appreciated!

Thanks,
Simon.



--
-
* Please reply to group for the benefit of all
* Found the answer to your own question? Post it!
* Get a useful reply to one of your posts?...post an answer to another
one
* Search first, post later : http://www.google.co.uk/groups
* Want my email address? Ask me in a post...Cos2MuchSpamMakesUFat!
Click to expand...
Click to expand...



--
 
Back
Top