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!



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


  3. Ken Sheridan

    Ken Sheridan
    Expand Collapse
    Guest

    Simon:

    Try this:

    SELECT idAllocation, Reading_Date, Gas_Reading , Electric_Reading,
    (SELECT Gas_Reading
    FROM Readings As R2
    WHERE R2.Reading_Date =
    (SELECT MAX(Reading_Date)
    FROM Readings AS R3
    WHERE R3.Reading_Date < R1.Reading_Date)) As
    Previous_Gas_Reading,
    (SELECT Electric_Reading
    FROM Readings As R4
    WHERE R4.Reading_Date =
    (SELECT MAX(Reading_Date)
    FROM Readings AS R5
    WHERE R5.Reading_Date < R1.Reading_Date)) As
    Previous_Electric_Reading
    FROM Readings AS R1
    ORDER BY Reading_Date DESC;

    Ken Sheridan
    Stafford, England

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

Share This Page