I have two tables
Product
Outgoing (I have not made a relationship) the only fields the same in these two tables are type and date - further any way i do make a relationship no info displays in the query)
I have made a query pulling down from both product table and outgoing table. with the intent to show how many product items are left in stock.
Expand|Select|Wrap|Line Numbers
- [Qty]-[Qty out]
the SQL for the outgoing query is as follows
Expand|Select|Wrap|Line Numbers
- SELECT OutgoingTBL.ID, OutgoingTBL.Date, OutgoingTBL.Type, OutgoingTBL.Size, OutgoingTBL.[Qty Out], [Qty]-[Qty Out] AS [Current Tyre Type in stock], OutgoingTBL.Freight, OutgoingTBL.[Cost Price], Products.QTY
- FROM OutgoingTBL, Products
- GROUP BY OutgoingTBL.ID, OutgoingTBL.Date, OutgoingTBL.Type, OutgoingTBL.Size, OutgoingTBL.[Qty Out], OutgoingTBL.Freight, OutgoingTBL.[Cost Price], Products.QTY;