Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

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

Posted on 2016-11-08
13
Medium Priority
?
423 Views
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?

Thanks!
0
Comment
Question by:Dustin Stanley
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 6
  • 4
  • 2
  • +1
13 Comments
 
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:

tProduct
-----------------
ProductID
Name
Description
etc etc

tTransaction
--------------------
TransID
ProductID
TransType
TransDate
Quantity
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).
0
 
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.

Jim.
0
 
LVL 39

Assisted Solution

by:PatHartman
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.
0
How Blockchain Is Impacting Every Industry

Blockchain expert Alex Tapscott talks to Acronis VP Frank Jablonski about this revolutionary technology and how it's making inroads into other industries and facets of everyday life.

 

Author Comment

by:Dustin Stanley
ID: 41880798
Scott
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.
 Example:
(Lets use Websites)

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

**THEN**
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"

________________________________________________________________

Pat
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:
db4.jpg
0
 
LVL 39

Accepted Solution

by:
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.
0
 

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:

QtyAdvertised
MarketName
ProductID
**(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
QtySold
ProductID


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

Markets.QtyAdvertised - OrderDetails.QtySold
**Criteria**
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.
0
 
LVL 39

Expert Comment

by:PatHartman
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.
0
 

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.
0
 

Author Closing Comment

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

Expert Comment

by:PatHartman
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.
0
 

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.
0
 
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.
1
 

Author Comment

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

Featured Post

Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

Question has a verified solution.

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

The Windows Phone Theme Colours is a tight, powerful, and well balanced palette. This tiny Access application makes it a snap to select and pick a value. And it doubles as an intro to implementing WithEvents, one of Access' hidden gems.
There are times when I have encountered the need to decompress a response from a PHP request. This is how it's done, but you must have control of the request and you can set the Accept-Encoding header.
In Microsoft Access, learn how to use Dlookup and other domain aggregate functions and one method of specifying a string value within a string. Specify the first argument, which is the expression to be returned: Specify the second argument, which …
Do you want to know how to make a graph with Microsoft Access? First, create a query with the data for the chart. Then make a blank form and add a chart control. This video also shows how to change what data is displayed on the graph as well as form…

670 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