Welcome to SPN

Register and Join the most happening forum of Sikh community & intellectuals from around the world.

Sign Up Now!

Previous Value

Discussion in 'Information Technology' started by Simon Harris, Jul 28, 2006.

Tags:
  1. Simon Harris

    Simon Harris
    Expand Collapse
    Guest

    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!
     
  2. Loading...


  3. Barry Gilbert

    Barry Gilbert
    Expand Collapse
    Guest

    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" wrote:

    > 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!
    >
    >
    >
     
  4. Simon Harris

    Simon Harris
    Expand Collapse
    Guest

    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" <BarryGilbert@discussions.microsoft.com> wrote in message
    news:8F3D9B0B-E9DF-4D6D-A8C1-E4EDFC674CB6@microsoft.com...
    > 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" wrote:
    >
    >> 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!
    >>
    >>
    >>
     
  5. Barry Gilbert

    Barry Gilbert
    Expand Collapse
    Guest

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

    Barry

    "Simon Harris" wrote:

    > 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" <BarryGilbert@discussions.microsoft.com> wrote in message
    > news:8F3D9B0B-E9DF-4D6D-A8C1-E4EDFC674CB6@microsoft.com...
    > > 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" wrote:
    > >
    > >> 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!
    > >>
    > >>
    > >>

    >
    >
    >
     
  6. Michael H

    Michael H
    Expand Collapse
    Guest

    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" wrote:

    > 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!
    >
    >
    >
     
  7. Simon Harris

    Simon Harris
    Expand Collapse
    Guest

    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" <BarryGilbert@discussions.microsoft.com> wrote in message
    news:8F3D9B0B-E9DF-4D6D-A8C1-E4EDFC674CB6@microsoft.com...
    > 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" wrote:
    >
    >> 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!
    >>
    >>
    >>




    --
    ----------------------------------------
    I am using the free version of SPAMfighter for private users.
    It has removed 4663 spam emails to date.
    Paying users do not have this message in their emails.
    Get the free SPAMfighter here: http://www.spamfighter.com/len
     
  8. Simon Harris

    Simon Harris
    Expand Collapse
    Guest

    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" <BarryGilbert@discussions.microsoft.com> wrote in message
    news:9323A702-3899-416D-A65D-442F9F7A4D7A@microsoft.com...
    > My example supposes that IdAllocation is an autonumber column. If it's
    > not,
    > this won't work.
    >
    > Barry
    >
    > "Simon Harris" wrote:
    >
    >> 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" <BarryGilbert@discussions.microsoft.com> wrote in message
    >> news:8F3D9B0B-E9DF-4D6D-A8C1-E4EDFC674CB6@microsoft.com...
    >> > 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" wrote:
    >> >
    >> >> 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!
    >> >>
    >> >>
    >> >>

    >>
    >>
    >>
     
  9. Simon Harris

    Simon Harris
    Expand Collapse
    Guest

    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" <BarryGilbert@discussions.microsoft.com> wrote in message
    news:9323A702-3899-416D-A65D-442F9F7A4D7A@microsoft.com...
    > My example supposes that IdAllocation is an autonumber column. If it's
    > not,
    > this won't work.
    >
    > Barry
    >
    > "Simon Harris" wrote:
    >
    >> 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" <BarryGilbert@discussions.microsoft.com> wrote in message
    >> news:8F3D9B0B-E9DF-4D6D-A8C1-E4EDFC674CB6@microsoft.com...
    >> > 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" wrote:
    >> >
    >> >> 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!
    >> >>
    >> >>
    >> >>

    >>
    >>
    >>




    --
    ----------------------------------------
    I am using the free version of SPAMfighter for private users.
    It has removed 4686 spam emails to date.
    Paying users do not have this message in their emails.
    Get the free SPAMfighter here: http://www.spamfighter.com/len
     
  10. Simon Harris

    Simon Harris
    Expand Collapse
    Guest

    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" <MichaelH@discussions.microsoft.com> wrote in message
    news:91EF1C3A-C6CC-4C10-8736-E6C4650677ED@microsoft.com...
    > 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" wrote:
    >
    >> 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!
    >>
    >>
    >>




    --
    ----------------------------------------
    I am using the free version of SPAMfighter for private users.
    It has removed 4686 spam emails to date.
    Paying users do not have this message in their emails.
    Get the free SPAMfighter here: http://www.spamfighter.com/len
     

Share This Page