Go Premium for a chance to win a PS4. Enter to Win


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

Posted on 2016-11-08
Medium Priority
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 85

Assisted Solution

by:Scott McDaniel (Microsoft Access MVP - EE MVE )
Scott McDaniel (Microsoft Access MVP - EE MVE ) earned 500 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 58

Assisted Solution

by:Jim Dettman (Microsoft MVP/ EE MVE)
Jim Dettman (Microsoft MVP/ EE MVE) earned 500 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 40

Assisted Solution

PatHartman earned 1000 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.
Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.


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 40

Accepted Solution

PatHartman earned 1000 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 40

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 40

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 85
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

What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

Question has a verified solution.

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

Traditionally, the method to display pictures in Access forms and reports is to first download them from URLs to a folder, record the path in a table and then let the form or report pull the pictures from that folder. But why not let Windows retr…
Windows Explorer let you handle zip folders nearly as any other folder: Copy, move, change, and delete, etc. In VBA you can also handle normal files and folders, but zip folders takes a little more - and that you'll find here.
What’s inside an Access Desktop Database. Will look at the basic interface, Navigation Pane (Database Container), Tables, Queries, Forms, Report, Macro’s, and VBA code.
This lesson discusses how to use a Mainform + Subforms in Microsoft Access to find and enter data for payments on orders. The sample data comes from a custom shop that builds and sells movable storage structures that are delivered to your property. …

971 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