Results 1 to 3 of 3

Thread: Query problem

  1. #1
    Join Date
    Feb 2005
    Posts
    1

    Unanswered: Query problem

    Hello,
    Can someone help me out? I'm new to MS Access and SQL so, I have now to solve a problem but I do not know how.
    Because of that I had to post my problem on this forum and I might get a solution for it. And here is: how could I refer to a certain row (number) using SQL. (I have to tell that I solved this problem but in and with FoxPro but not with SQL). Shortly, how coul I refer the previous row data from a certain field to the next one. Thank you very much,
    Kangoo
    Attached Thumbnails Attached Thumbnails screen.jpg  

  2. #2
    Join Date
    Jan 2005
    Posts
    31
    The basic answer is, you can't. There are no "current row/previous row" operators or methods in SQL.

    A relational database isn't a flat file system, you aren't "iterating" through the rows one at a time. It is operating on them in a "set" operation system.

    If you *must* iterate through the rows and keep track of the contents of the previous rows, you must write VBA, instantiate a recordset (aka "Cursor"), iterate through it using the recordset methods (.MoveFirst, .MoveNext, etc.); and then use the recordset to populate a report or a update a table to keep track of the results.

  3. #3
    Join Date
    Jan 2004
    Location
    Aberdeen, Scotland
    Posts
    1,067
    It is possible kind of heres an example of how access can do this
    Code:
    SELECT ChangeDate, 
                  StockGathered,
                  StockUsed,
                   DSum("StockGathered", "tblStock", "ChangeDate <= #" & Format([changedate],"mm/dd/yyyy") &  "#" )
                         - DSum("stockused", "tblStock", "ChangeDate <= #" & Format([changedate],"mm/dd/yyyy") & "#") AS StockRemaining
    FROM tblStock;
    but from your layout what you are really after looks like a spreadsheet which is designed for this kind of task

Posting Permissions

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