Solved

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

Posted on 2016-11-08
13
45 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
  • 6
  • 4
  • 2
  • +1
13 Comments
 
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:

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

Jim.
0
 
LVL 34

Assisted Solution

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

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 34

Accepted Solution

by:
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.
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
Maximize Your Threat Intelligence Reporting

Reporting is one of the most important and least talked about aspects of a world-class threat intelligence program. Here’s how to do it right.

 
LVL 34

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 34

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

Author Comment

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

Featured Post

How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

Join & Write a Comment

Overview: This article:       (a) explains one principle method to cross-reference invoice items in Quickbooks®       (b) explores the reasons one might need to cross-reference invoice items       (c) provides a sample process for creating a M…
I see at least one EE question a week that pertains to using temporary tables in MS Access.  But surprisingly, I was unable to find a single article devoted solely to this topic. I don’t intend to describe all of the uses of temporary tables in t…
Familiarize people with the process of utilizing SQL Server views from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Access…
Show developers how to use a criteria form to limit the data that appears on an Access report. It is a common requirement that users can specify the criteria for a report at runtime. The easiest way to accomplish this is using a criteria form that a…

760 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

Need Help in Real-Time?

Connect with top rated Experts

19 Experts available now in Live!

Get 1:1 Help Now