Minus values between tables

[0nyx175]

Hi guys,
Firstly apologies if I’m in the wrong place. I’m reacquainting myself with Access and have created a simple database for the logging of purchases through a store.
I have the following tables:



Customer
Customer_number
First_name
Surname


Item
Item code
Item name
Stock
Price


Purchasing
Purchase code
Customer number
Item Code
Amount


The purchasing table contains relationships between customer and item to log a purchase onto the system. What I’d like to do is when someone logs a purchase of say an ipad, the table automatically updates the level of stock in the item field to minus one. So for example I buy an ipad, the system now knows there is one less ipad in stock.
Can anyone suggest the best way to do this?



Many thanks

 

[jzwp22]

Welcome to the forum!

Can many items be purchased during 1 purchasing event? If so, your current table structure will not handle that.

Also, you should not need the stock field at all. You would calculate the current inventory for each item via a query.

 

[0nyx175]

Welcome to the forum!

Can many items be purchased during 1 purchasing event? If so, your current table structure will not handle that.

Also, you should not need the stock field at all. You would calculate the current inventory for each item via a query.

Thanks for the welcome. Only one item can be purchased per purchasing event. I tried using a query but it simply takes all stock of all items and minuses it against all purchases of all items so it's bulk stock rather than item.

For example i have the following stock

10 ipads
20 blackberries

If i purchase one and run a query against stock bought vs stock available it shows 9 ipads and 19 blackberries

 

[jzwp22]

Thanks for the welcome. Only one item can be purchased per purchasing event. I tried using a query but it simply takes all stock of all items and minuses it against all purchases of all items so it's bulk stock rather than item.

You will have to use an aggregate query that groups by the item code value.

How do you distinguish the purchasing of items (to add to inventory) from sales of items (removal from inventory)?

Allen Browne has some tips on his website for dealing with inventory on hand if you want another approach. He also provides a table structure that you could adapt to your application.