alain.roger
Registered User.
- Local time
- Today, 19:45
- Joined
- Nov 1, 2014
- Messages
- 18
Hi,
i'm trying to refer to previous record in a query and i'm stuck as i do not know how to do it.
Here is my query:
in fact the line: IIF([Missing] IS NULL,0,[Prod2Buy].[Quantity To Order]-[To Order]) AS [Rest To Order]
should refer to previous record but i do not know how to cope with it
any help would be great.
Thx
i'm trying to refer to previous record in a query and i'm stuck as i do not know how to do it.
Here is my query:
Code:
SELECT
[Prod2Buy].[Product Code],
IIF([Missing] IS NULL,0,[Prod2Buy].[Quantity To Order]-[To Order]) AS [Rest To Order],
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
in fact the line: IIF([Missing] IS NULL,0,[Prod2Buy].[Quantity To Order]-[To Order]) AS [Rest To Order]
should refer to previous record but i do not know how to cope with it
any help would be great.
Thx