Results 1 to 4 of 4
  1. #1
    Join Date
    Feb 2013
    Posts
    5

    Unanswered: Refer to previous record in a query

    Hi,

    i'm trying to refer to a previous record inside a query.
    How ca i do it ?

    for example here is my SQL Query:
    HTML Code:
    SELECT 
     [Prod2Buy].[Product Code],
     IIF([Missing] IS NULL,0,[Prod2Buy].[Quantity To Order]-[To Order]) AS [Rest To Order], <<<---- this should refere to previous record of the same query
     s.s_manu_code AS [Manufacturer Code],
     s.prod_cost AS [Price Code],
     IIF(s.supp_stock IS NULL,[Rest To Order],IIF(s.supp_stock<=[Rest To Order],s.supp_stock,"")) AS [To Order],
     ([Prod2Buy].[Quantity To Order]-s.supp_stock) AS [Missing]
    FROM
     [SM1 Query] AS [Prod2Buy],
     (SELECT s.s_prod_code,s.s_manu_code, s.prod_cost, s.supp_stock FROM supplier_prod AS s,[SM1 Query] AS [Prod2Buy] WHERE s.s_prod_code=[Prod2Buy].[Product Code] ORDER BY prod_cost ASC)
    WHERE
     Prod2Buy.[Promotional Product]="Yes"
    AND
     [Prod2Buy].[Product Code]=s.s_prod_code
    ORDER BY
     s.prod_cost ASC
    IIF([Missing] IS NULL,0,[Prod2Buy].[Quantity To Order]-[To Order]) AS [Rest To Order], <<<---- this should refere to previous record of the same query

  2. #2
    Join Date
    Apr 2014
    Location
    Kentucky
    Posts
    495
    Provided Answers: 24
    Unless you use sequential keys, you cant, because queries dont know the order, you do. A record doesnt know what the other record is doing.
    If you write the result to a table with autonumber, then yes, just refer to the previous key.

    also You could if you scan the records in a loop.

  3. #3
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    The query you posted has an ORDER BY clause:
    Code:
    ORDER BY s.prod_cost ASC
    This means that the resulting data set is sorted on the [prod_cost] column, ascending (from the lowest to the highest value). Therefore, a "previous row" in this data set would be the row with a [prod_cost] value immediately inferior to the one of the current row. Is it what you want?
    Have a nice day!

  4. #4
    Join Date
    Jun 2005
    Location
    Richmond, Virginia USA
    Posts
    2,763
    Provided Answers: 19
    Maybe Allen Browne's article on doing just that will help:

    http://allenbrowne.com/subquery-01.html#AnotherRecord

    Linq ;0)>
    Hope this helps!

    The problem with making anything foolproof...is that fools are so darn ingenious!

    All posts/responses based on Access 2003/2007

Posting Permissions

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