Embedded SQL statement in ACCESS （急）20分

I think you are using Access VBA. Is Order_number.Value a textbox on a form?

Your sql statement looks like this:

SELECT SUM( [ORDER-PRODUCT].Quantity * PRODUCT.Unit_price) AS Total_Amount
FROM PRODUCT INNER JOIN [ORDER-PRODUCT] ON PRODUCT.[Product code]=[ORDER-PRODUCT].[Product code]
WHERE [ORDER-PRODUCT].[order number] =' " & Order_number.Value & " ' ;"

This sql statement returns you the total amount of all products on ONE order.

2008-07-04 05:18:53 補充：
不能同上面的 sql statement 一齊做，要分開做，因為上面的 sql 是將不同的產品加在一起計算總值，現在要每一產品逐一比較。

2008-07-04 05:25:50 補充：
SELECT [ORDER-PRODUCT].[order number], [ORDER-PRODUCT].[Product code],
PRODUCT.[Stock Quantity] - [ORDER-PRODUCT].Quantity AS Surplus
FROM PRODUCT INNER JOIN [ORDER-PRODUCT]
ON PRODUCT.[Product code]=[ORDER-PRODUCT].[Product code]
WHERE [ORDER-PRODUCT].[order number] =' " & Order_number.Value & " ' ;"

2008-07-04 05:26:06 補充：
Surplus < 0 => out of stock

2008-07-04 05:28:59 補充：
SELECT [ORDER-PRODUCT].*
FROM PRODUCT INNER JOIN [ORDER-PRODUCT]
ON PRODUCT.[Product code]=[ORDER-PRODUCT].[Product code]
WHERE PRODUCT.[Stock Quantity] > [ORDER-PRODUCT].Quantity
AND [ORDER-PRODUCT].[order number] =' " & Order_number.Value & " ' ;"

2008-07-04 05:30:24 補充：
Only orders with sufficient stock are returned.