Hi, i'm new here and i'm trying to create a stock control system for an Entertainment store.
I haven't really got very far at the moment. I've firgured out a few of the tables I need, i've got Stock Levels and Sales tables, which are both going to be related to 3 tables which will be of CDs, DVDs and Video-Games. I need to be able to automatically update stock levels going by the sales that have happened with a notification popping up when the stock level gets too low. I think I can do the last bit but I just really need some help getting started.
Thanks in advance.
Okay, ignore the above post, now I have a different problem.
I've managed to get it updating but it just looks up the Product ID in the Sales Table and changes the corresponding Product's Product Quantity to -1 and I have no idea why. Heres the SQL for the Update Query:
UPDATE tbl_Product INNER JOIN tbl_Sales ON [tbl_Product].[ProductID]=[tbl_Sales].[ProductID] SET tbl_Product.[Product Quantity] = ([tbl_Product]![ProductID]=[tbl_Sales]![ProductID] AND ([tbl_Product]![Product Quantity]-[tbl_Sales]![Quantity Sold]));
So basically, all Products in the Sales Table have been compared to the Product ID in the Product table and the values of Product Quantity has been changed to -1, instead of doing what I believe the query is supposed to do and subtracting the Quantity sold from Product Quantity.