MS Access Table Set Up For Keeping Track Of Inventory When Selling On Multiple Locations

Posted on 2016-11-08
Last Modified: 2016-11-12
How would you setup a table in Access to keep track of Inventory quantities when selling in multiple Locations.

Say you have a Toy Bus with a Quantity of 10. You sell from multiple websites or multiple store locations. But you don't want to sell more then you have.  How can you show you have:

7 In Store"A"
3 In Store"B"

Also does Access have some kind of Stock Level Alert?

Question by:Dustin Stanley
  • 6
  • 4
  • 2
  • +1
LVL 84

Assisted Solution

by:Scott McDaniel (Microsoft Access MVP - EE MVE )
Scott McDaniel (Microsoft Access MVP - EE MVE ) earned 125 total points
ID: 41880300
Inventory is generally maintained using transaction tables. You'd store your Products in one table, and then you'd have a variety of "transactions" in a table relating to the various methods you employ for inventory, and you would then create queries to provide you with data regarding the Inventory and/or the processes/methods performed on that Inventory. For example:

etc etc

etc etc

So in your Transaction table you'd store "events" relevant to your Products. When you ship items to your multiple locations, for example, you'd enter a "Shipment" TransType with a negative quantity (since you're decreasing your inventory), and your "receiving" location would enter a positive quantity. When you receive (or create) new products, you'd enter a "Receipt" TransType with a Positive Quantity.

To store information about Products at a specific Location, you'd you'd add a Location table, and add a column in the Transaction table to store the Location.

You can also enter a "BeginningBalance" TransType to indicate quantities of each Product when you begin using the system. This gives you the beginning balance, and from there you should be able to calculate the exact amount of Inventory at any point in time, for any Product.

You can also use "StockTakes", which are physical inventory counts (since inventory seems to shrink and grow at will).
LVL 57

Assisted Solution

by:Jim Dettman (Microsoft MVP/ EE MVE)
Jim Dettman (Microsoft MVP/ EE MVE) earned 125 total points
ID: 41880342
To add a bit to what Scott said, you'd also have a "Warehouse" table and you would record the warehouse along with your transactions.  

You'd also need a "Locations" table to track the locations in a warehouse and what's in each.

LVL 36

Assisted Solution

PatHartman earned 250 total points
ID: 41880740
Also does Access have some kind of Stock Level Alert?
This is an application specific requirement.  It has nothing to do with Access.  You would need to program alerts to suit your requirements.  Generally, each product would have a minimum quantity level and when the inventory goes below that you would be alerted.  How frequently the levels are checked and how and whom is alerted is all up to you to define.
Three Reasons Why Backup is Strategic

Backup is strategic to your business because your data is strategic to your business. Without backup, your business will fail. This white paper explains why it is vital for you to design and immediately execute a backup strategy to protect 100 percent of your data.


Author Comment

by:Dustin Stanley
ID: 41880798
To store information about Products at a specific Location
This is the part I am truly asking about. I have all the actual inventory tables set up. What  I need to know is how to keep track of quantity of items advertised in a specific location.
(Lets use Websites)

If I have 10 then:
Website "A" I advertise 10
Website "B" I advertise 10

Website "A" Someone buys 9
Website "B" Someone buys 6

 In that case we have to tell someone that we sold more then we had and then have to cancel an order. That would be horrible!

I am trying to keep away from saying we have 10 on website "A" and 10 on website "B"

What I would like to keep track of is:

Website "A" I advertise 6
Website "B" I advertise 4

Website "A" sells all 6 then I can take some from Website "B" and place them on Website "A"


This is an application specific requirement.  
I get that but at the end of the post this part kinda popped up in my head. i would have a stock level alert field in my table. Would I program something to say hey once this stock level alert field is at or lower then I specified make a popup alert form come up or some bells and whistles happen in Access.

I didn't know if there was some way access has set up some kind of (I don't know exactly what to say here but) complimentary service for this already.
I was thinking of a "Markets Table" = A table that I could say this Market has this amount of quantity and this other Market has this amount of quantity. This would be linked back to my Products or SKU Table. Unsure which one at this time.

Thanks for the help Guys!
Here is my relationships currently. It might help with some questions of what I currently have:
LVL 36

Accepted Solution

PatHartman earned 250 total points
ID: 41880999
If you have separate databases to power each website, then there is no easy way to manage inventory.  If all websites are linked to the same database, then when you sell 10 on siteA, the transaction is entered in the common database and is visible to all websites.

If you keep separate databases, how often are they synchronized?  You could use the method my best friend employed when she got married and shared a checking account with her new husband.  She kept a "master checkbook and then "wrote" checks to her husband's account and to hers.  they could each only spend what they had in their own ledger even though they new there was more money in the account.  You can do a similar thing and use transactions to transfer inventory to various sites.  The key to making this work will be the ability of each site to query availability on other sites if they didn't have sufficient quantity.

Author Comment

by:Dustin Stanley
ID: 41881364
Ok that will give me something to ponder about as in website capabilities. I have one database for all the inventory. I know it will be easier for me when they are my websites but if there are 3rd party websites involved that is what i am trying to configure.  They do have APIs and such and that will definitely be later but I was thinking if I had this Markets Table or something like that it may help when doing everything manually for now and it would work for physicall locations.

The table would keep track of the quantity I have advertised for a specific location (Store or Website)

Fields like:

**(and this is where I don't know what other fields might help)**

Then in my Orders and OrderDetails Table I could have fields:
MarketName 'Flag where it sold at

I could come up with a calculation query or something to auto deduct Like this

Markets.QtyAdvertised - OrderDetails.QtySold
ProductID = ProductID and MarketName = MarketName

After that I could keep track of the Market Advertising Totals Vs Our true overall inventory total.

I could then increase/decrease items as needed in different locations to spread out the advertising.

This could be done manually or by a calculation of a percent and then sent out by API!

Just what I'm thinking! I hope that makes sense.
LVL 36

Expert Comment

ID: 41882464
I'm confused.  Do you or do you not use a single database for ALL the websites?  If you don't, then you will need to keep inventory by "location" and actually move inventory from "location" to "location" as needed.  You can do this in a batch process at night that reconciles inventory between all the separate website databases and your local master database.

Many applications use "location" or something similar such as "warehouse" to track where inventory actually is.  It may even go down to row and bin to facilitate picking applications.  When an app generates a pick list in this case, it would be organized to have the picker (person or bot) take the shortest path through the warehouse.

Author Comment

by:Dustin Stanley
ID: 41883244
I only use one database for all my products. Now that you have me thinking.
On 3rd party websites...When you advertise items through them they keep track of your inventory that is advertised.

This you might consider (and in truth probably is) a database.

It is just a list of what you are selling and how many you told them you have available.

I (as in me and my business) have a single access database.

That is where I was thinking about the markets table and involving this question. On my single database.

I hope that makes sense.

I really have to figure something out . Because I would like to advertise in different venues for better revenue but some of these 3rd party locations will look down on you if sell more then you have and have to refund customers.

This leads into you not being able to advertise with them.

Author Closing Comment

by:Dustin Stanley
ID: 41883834
Thanks for the info!
LVL 36

Expert Comment

ID: 41883882
If the websites are not connected to your database then you have to maintain separate inventory for them.  Websites that sell things are always connected to a database.  It just may not be yours.

Author Comment

by:Dustin Stanley
ID: 41884321
I know I already closed this question and thank you but could I put in my database a table of its own of each market I sell at. Say table called Market1 and then have fields that relate to that market only. Or would this be redundant in the database?

If you want i could open another question as this.
LVL 84
ID: 41884666
No, you should not do that. You could have a Markets table where you'd store information about each Market, and an Inventory table where you would store Inventory information about the items you're advertising in each Market.

When considering issues like this, one way to determine if the idea is a good one is to ask yourself "What would happen down the road if I had to do xyz". In this case, the question would be "What would happen down the road if I had to add a new Market". If the answer is "Add a new table" then chances are you're on the wrong path. If the answer is "Add a new Row to my Markets table", then chances are you're on the right path.

Author Comment

by:Dustin Stanley
ID: 41884870
Makes sense. Thanks Scott!

Featured Post

Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Some code to ensure data integrity when using macros within Excel. Also included code that helps secure your data within an Excel workbook.
Phishing attempts can come in all forms, shapes and sizes. No matter how familiar you think you are with them, always remember to take extra precaution when opening an email with attachments or links.
Learn how to number pages in an Access report over each group. Activate two pass printing by referencing the pages property: Add code to the Page Footers OnFormat event to capture the pages as there occur for each group. Use the pages property to …
In Microsoft Access, when working with VBA, learn some techniques for writing readable and easily maintained code.

821 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question