By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
434,998 Members | 2,825 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 434,998 IT Pros & Developers. It's quick & easy.

How do I write an SQL in Access to compare data in the previous row to return a value

P: 1
I am using the following sql query via ODBC to pull data from a database into Access.
Expand|Select|Wrap|Line Numbers
  1. SELECT table.excav_id,
  2.        table.RecordID, 
  3.        table.indx_dpr, 
  4.        table.tons_bkt, 
  5.        table.tons_trk
  6. FROM table
  7. WHERE (((table.Record_Date)>=#8/28/2008 6:15:00#))
  8. ORDER BY table.excav_id, table.RecordID, table.indx_dpr,table.Record_Date;
In the query result below, the value in the column ‘tons_trk’ is the running total of the ‘tons_bkt’ for loading a truck. The column ‘Load_#’ does not exist in the dataset, but it is what I would like to populate to identify each individual load. It can just be a sequencial value as indicated..

The way to identify the total segment for a single load would be to compare the value in the previous record "tons_trk" to the current record "tons_trk" and if it is less than, start a new load_#. I just do not know how to write the sql query to accomplish this.
Expand|Select|Wrap|Line Numbers
  1. RecordID    excav_id    indx_dpr    tons_bkt    tons_trk    load_#
  2. 1923367    1    1    54    54    1
  3. 1923368    1    2    56    110    1
  4. 1923369    1    3    54    165    1
  5. 1923370    1    4    48    213    1
  6. 1923371    1    5    39    252    1
  7. 1923372    1    1    68    68    2
  8. 1923373    1    2    63    131    2
  9. 1923374    1    3    58    189    2
  10. 1923375    1    4    55    243    2
  11. 1923376    1    1    65    65    3
  12. 1923377    1    2    67    133    3
  13. 1923378    1    3    69    202    3
  14. 1923379    1    4    61    263    3
  15. 1923380    1    1    34    34    4
  16. 1923381    1    2    0    34    4
  17. 1923382    1    3    0    34    4
  18. 1923383    1    1    72    72    5
  19. 1923384    1    2    60    132    5
  20. 1923385    1    3    52    184    5
  21. 1923386    1    4    67    252    5
  22. 1923387    1    2    62    62    6
  23. 1923388    1    3    58    121    6
  24. 1923389    1    4    56    177    6
  25. 1923390    1    5    54    231    6
Thanks
Sep 3 '08 #1
Share this Question
Share on Google+
1 Reply


puppydogbuddy
Expert 100+
P: 1,923
The following links discuss and give examples of 3 methods you can use:
1.DLookup
2.User Defined Function (UDF)
3.Subquery

http://support.microsoft.com/kb/210504
http://allenbrowne.com/subquery-01.html#AnotherRecord
Sep 7 '08 #2

Post your reply

Sign in to post your reply or Sign up for a free account.