What is the supporting code with Queries?

[nicksource]

As an example, I have 2 separate queries:

SELECT Orders.QtyAdd
FROM Orders
WHERE Orders.ProdID = 1;

SELECT Output.QtySub
FROM Output
WHERE Output.ProdID = 1;

What code do I use to subtract these 2 queries?

And also, where it says .ProdID = 1; what is the variable so it picks the ProdID from a basic single row form being shown? I guess it doesn't follow the Table.Field pattern?

Thanks, hope that isn't confusing.

 

[wazz]

"I guess it doesn't follow the Table.Field pattern?"

yes it does (if i understand). Orders.ProdID and Output.ProdID follow that pattern. Orders is a table and Output is a table. ProdID is a field you can find in both of those tables (a foreign key field to the Product table). hth. bed time.

 

[nicksource]

Sorry, I mean if Forms follow this, like Form.Textfield. So I can do something like:

Order.ProdID = Form.TextField

 

[nicksource]

Here's what I'm trying but with no luck:

SELECT Sum([Orders.QtyAdd]-[Output.QtySub]) AS Expr1
FROM (Products INNER JOIN Orders ON Products.ProdID = Orders.ProdID) INNER JOIN [Output] ON Products.ProdID = Output.ProdID
WHERE (((Orders.StockItem)=Yes) AND ((Orders.Arrival)=Yes) AND ((Output.Dispatched)=Yes)) AND Products.ProdID = 2 ;

 

[pbaldy]

Here's a good reference on how to refer to forms:

http://www.mvps.org/access/forms/frm0031.htm