Results 1 to 12 of 12
  1. #1
    Join Date
    Jul 2011
    Posts
    5

    Unanswered: Calculating Previous record

    Hi all,

    I am having some difficulty with a SQL query to show the deference between previous and current record. The query works fine if previous record is less than current record. I'm having trouble when the previous record value is the same as the current value or a zero is entered. Below is query I have now..


    SELECT date, table.value, (SELECT MAX(value) FROM table AS Pure WHERE Pure.value < table.value) AS Prevalue, table.value-Prevalue AS Difference
    FROM table

    Example when value is less than current.

    Date Value Prev Diff
    5/4/09 650
    5/5/09 850 650 200
    5/6/09 950 850 100
    5/7/09 1000 950 50

    Example when previous value is the same

    Date Value Prev Diff

    5/4/03 650
    5/5/09 850 650 200
    5/6/09 850 650 200
    5/7/09 1000 850 150

    What I would like to see is...

    value Prev Diff

    650
    850 650 200
    850 850 0
    1000 850 150


    I'm hoping someone could help.

    Thanks, Dan.

  2. #2
    Join Date
    May 2005
    Location
    Nevada, USA
    Posts
    2,888
    Provided Answers: 6
    Given your example, I'd expect the WHERE clause to use the date field, not the value field, presuming there aren't two records on the same date.
    Paul

  3. #3
    Join Date
    Jul 2011
    Posts
    5
    Yes, it could be more than one record per day. What I ultimately want is to compare the previous record regardless of how many entries per day. I get the same problem if I do date or value. I'm very new at sql and access so I'm not really sure how to accomplish this task.
    Last edited by Danbbhcg; 07-21-11 at 16:51.

  4. #4
    Join Date
    May 2005
    Location
    Nevada, USA
    Posts
    2,888
    Provided Answers: 6
    Is there an ID field that could be combined with date to determine the previous record?
    Paul

  5. #5
    Join Date
    Jul 2011
    Posts
    5
    Yes, Sorry for not giving you all the proper info. My table looks like this


    ID(pk), Date, Value,

  6. #6
    Join Date
    May 2005
    Location
    Nevada, USA
    Posts
    2,888
    Provided Answers: 6
    How about

    (SELECT Max(value) FROM table AS Pure WHERE Pure.Date <= table.Date AND Pure.ID < table.ID)

    This may help, though it's slightly different. I think you want TOP 1 with an ORDER BY clause rather than Max():

    Microsoft Access tips: Subquery basics
    Paul

  7. #7
    Join Date
    Nov 2010
    Posts
    84
    If you assign the ID column which is AutoNumber then the solutions in the following query

    SELECT Table.ID, Table.Date, Table.Value, DLookUp("[Value]","Table","ID=" & [ID]-1) AS Prev, [Value]-[Prev] AS Diff
    FROM [Table];

  8. #8
    Join Date
    Jul 2011
    Posts
    5
    It worked! Thank you both for all your help, both of the query's worked. Quick question, how do I sum the difference? Can I do that in the same query?

    Thanks again, Dan.

  9. #9
    Join Date
    Nov 2010
    Posts
    84
    SELECT Table.Date, Table.Value, DLookUp("[Value]","Table","ID=" & [ID]-1) AS Prev, [Value]-[Prev] AS Diff, (SELECT Sum(Value- DLookUp("[Value]","Table","ID=" & [ID]-1)) FROM [Table] AS temp WHERE temp.[ID] <= [Table].[ID]) AS SumDiff
    FROM [Table];

  10. #10
    Join Date
    Jul 2011
    Posts
    5
    I thank you both for all the help, have a great weekend!


    Regards, Mario.

  11. #11
    Join Date
    May 2005
    Location
    Nevada, USA
    Posts
    2,888
    Provided Answers: 6
    You too, Dan/Mario!
    Paul

  12. #12
    Join Date
    Nov 2010
    Posts
    84
    You have such an attachment to an editable form
    Attached Files Attached Files

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •