Beginner Question - Updating 1 table based on records in another (Inventory & Sales)

[zkinguk]

Good evening,



I am trying to create an access database to track sales, orders, and inventory by exporting data from an online system and appending them to this access database. The idea, which I 'may be thinking about wrong is to update the inventory levels in the inventory table based on all of the transactions in the sales table. Once I can do that I will create a similar "Orders," Table.

My initial thought was to use some sort of update query or a calculated field to be able to do this by joining on item number. Can someone please help?

I want to be able to run some reports out of this data source and really need some help setting this up. Thanks.

Item Number	ItemID	Overarching Categories	Piece	Size	Wholesale Price	Retail Price	Base Inventory Levels

Order Number	Popup Name	Customer FN	Customer LN	Customer Email	Item Number	Item Quantity	Order Date

[zkinguk]

Please if anyone can assist I am stuck.

[orange]

Stuck on what exactly?

Describe a day in you business. Start at the 30,000 ft level and gradually add details.
Build a model showing the table and relationships. Create some test data and scenarios; test the model.

You might get some ideas from this youtube video on Stock Control. There are lots of posts/forums with info on Inventory Management.
Here's a generic Customer-Inventory Management data model. and related specifications

Access will not do or know anything beyond what you tell it. Do your analysis and planning and design before getting too deeply involved in physical database.

With great respect, you are not nearly ready to do this

My initial thought was to use some sort of update query or a calculated field to be able to do this by joining on item number.

First step is to describe what you are trying to do. Bounce it off colleagues and adjust as necessary. Then do Analysis and get a design (data model or simple prototype) configured and tested.

Good luck