• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 577
  • Last Modified:

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

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?

Dustin Stanley
Dustin Stanley
  • 6
  • 4
  • 2
  • +1
4 Solutions
Scott McDaniel (Microsoft Access MVP - EE MVE )Infotrakker SoftwareCommented:
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).
Jim Dettman (Microsoft MVP/ EE MVE)PresidentCommented:
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.

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.

Dustin StanleyEntrepreneurAuthor Commented:
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:
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.
Dustin StanleyEntrepreneurAuthor Commented:
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.
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.
Dustin StanleyEntrepreneurAuthor Commented:
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.
Dustin StanleyEntrepreneurAuthor Commented:
Thanks for the info!
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.
Dustin StanleyEntrepreneurAuthor Commented:
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.
Scott McDaniel (Microsoft Access MVP - EE MVE )Infotrakker SoftwareCommented:
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.
Dustin StanleyEntrepreneurAuthor Commented:
Makes sense. Thanks Scott!

Featured Post

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

  • 6
  • 4
  • 2
  • +1
Tackle projects and never again get stuck behind a technical roadblock.
Join Now