Solved

How do I create an automated inventory count for accessory products in Microsoft Access?

Posted on 2014-10-01
14
298 Views
Last Modified: 2015-02-18
I'm developing a database that controls inventory for a distribution site. In this inventory list is products and supplies under products.
One product can have many supplies. I want to include a area under the supply area where the user can set a "In-stock" number and a "Reorder" number. I would like this to be on a dashboard if it is due for reorder.

When the distribution site sends out certain supplies I want it to send that number to a different area of the database recording this and deduct from the initial in-stock number.

I'm open to an expert connecting to my system to assist me with this.
0
Comment
Question by:OneByte
  • 7
  • 3
  • 2
  • +1
14 Comments
 
LVL 39

Expert Comment

by:als315
ID: 40355316
You can download Northwind database template from MS site:
http://office.microsoft.com/en-us/templates/results.aspx?ck=1&ex=2&qu=Northwind&av=all
it could be a good start point for you
0
 
LVL 84
ID: 40355322
You really shouldn't store an Inventory count. Instead, you should use something like a Transaction table to record the inflow/outflow of your inventory, and use that to determine how much of a specific item you have.

You may also need to include the concept of Stock Counts in this scenario. As I'm sure you're aware, inventory has a habit of disappearing at times, so you occasionally need to do a physical count to get things back in sync. When you do that, you'd then need to begin your counts at that last physical count, and add any new inflows, and subtract any new outflows. From there, you'd have an accurate count of the items.

Once you do this, you can then have your dashboard run a query to determine the current stock count of your Inventory, and compare that to your Reorder or Restock point. If it's lower, then your dashboard could indicate this.
0
 

Author Comment

by:OneByte
ID: 40355325
I have seen this and a number of other templates but still am not successful. I have made a few attempts.
0
 

Author Comment

by:OneByte
ID: 40355421
Als315: Thank you for your comment. Please see my last comment.

Scott McDaniel: That is exactly what I'm thinking is a Transaction table. I'm just not sure what fields to have in there, how to set this up, and how to make the calculations work. I completely get your second paragraph and this will be done for (as you have stated) count accuracy.

Please further guide me.
0
 
LVL 34

Expert Comment

by:PatHartman
ID: 40355532
Inventory management is incredibly complex and much depends on the type of inventory you are managing.  We couldn't even begin to offer a potential solution without lots more information.  Also, please don't expect an expert here to build the app for you unless you hire him.  We are here to solve problems and offer advice not to develop custom solutions.

What type of inventory are you managing?
- goods for sale (shoes)
- supplies for internal use (toilet paper)
- parts for manufacturing (screws)
- kits (subassemblies that are optionally handled as end items)

Do your items have expiration dates? (donuts)
Do your items have serial numbers? (cars)
Do your items have attributes? (color, size)
Do you have multiple warehouse locations?
Do you have to manage a BOM (Bill of Material)?

All of these questions impact how the data is stored and how it is used.   I would search for a pre-built product that is intended for your type of business.  It will cost less in the long run if you can find one.  I think even QuickBooks supports some inventory features.  Or, you might come up with an example that can get you started.  The only inventory applications I have written were for large manufacturing companies (airplanes, engines, catalogs) and those were COBOL/DB2 so I can't post examples.
0
 

Author Comment

by:OneByte
ID: 40356124
I was not expecting an expert to "build" it for me. I was merely asking the question to get a better idea of how to tackle this beast. I welcomed a remote connection at the advice of a Expert Exchange person.

Answering your questions:
What type of inventory are you managing?
Supplies for internal use
Do your items have expiration dates? No
Do your items have serial numbers? Yes
Do your items have attributes? No
Do you have multiple warehouse locations? Yes, I have different sites.
Do you have to manage a BOM (Bill of Material)? No

The database would be very small compared to the large databases you've created. This would include at most 200 products, and maybe only 20% would have supplies to track.

 I greatly appreciate your comments and any direction anyone can provide for me to move forward with this. Thank you.
0
6 Surprising Benefits of Threat Intelligence

All sorts of threat intelligence is available on the web. Intelligence you can learn from, and use to anticipate and prepare for future attacks.

 

Author Comment

by:OneByte
ID: 40358361
I understand the reason why it wouldn't be permitted to have a remote assistance session. Although that statement is true, I called in yesterday to get help in asking my question. The lady I spoke with asked if I was up to an expert connecting to my system. She said it would be good to put that in my question.
0
 
LVL 84
ID: 40359092
Can you give a little better description of your "supplies for internal use"?

Regarding serial numbers - do you need to track where a specific Serialiazed item is being used? For example, if you have ItemA, ItemB and ItemC, do you need to know that ItemA is being used by Employee1, and ItemB is being used by Employee55?
0
 

Author Comment

by:OneByte
ID: 40364190
Yes I can. Sorry for the timely response.

You hit it right on the nail. That is exactly what I'm looking to do with an Access Database. In some cases not all items will have serial numbers (I.E. a pack of paper towel - this might be purchased in bulk then distributed individually) and not all items will go to a particular person but rather a location.

It is for our Head Start Program. They would like to know who was given which cell phone, what box of crayons went to which location, etc. They would also like to keep track of supplies such as how many toilet paper rolls are in-stock, how many were distributed, and where they were sent to (by location), etc. Other items such as paper towel, cleaning supply bottles, and hygienic items would also need to be recorded.  

I have attached a copy of what I'm working on.
Database-1.accdb
0
 
LVL 84

Assisted Solution

by:Scott McDaniel (Microsoft Access MVP - EE MVE )
Scott McDaniel (Microsoft Access MVP - EE MVE ) earned 500 total points
ID: 40365618
So you're not actually "serializing" inventory, but rather tracking internal assets, each of which may or may not have a Serial Number. That's a bit different.

From what I can see, you'd need a Join table between Employees and Inventory:

tEmployee_Inventory
-----------------------------
ID
EmployeeID
InventoryID
DateDeployed
DateReturned
etc etc

If you do this, you'd be able track specific inventory items, and which Employee they're associated with.

If Inventory items are instead associated with a Location, then substitute that for Employee in the Join table.

Further: I don't think you need LocationID in the Inventory table, unless that refers to the "Home" location of the Inventory (and the same with LocationID in Employees).

You also don't need CompanyID in the Maintenance table, since you're already related the Inventory table, which contains that data. CompanyID has nothing to do with Maintenance (you're not performing Maintenance on Dell, you're performing Maintenance on a Dell Product).
0
 
LVL 34

Expert Comment

by:PatHartman
ID: 40366857
I can't look at the database because I can't download .accdb files due to a conflict between this site and IE so my comments are based on what others have said.

LocationID in the Employee table refers to the location of the Employee.
Location ID in the Inventory table refers to the location of the Inventory.

Assigning inventory to both Employees and locations can add a whole world of complexity to this app.  To keep it simple, I would create artificial Employee records for each Location.  That way if you send paper towels from the storage location to a different location, you can assign them to an "employee" and track the movement that way.  So everything moves from an inventory location to an employee of from an employee back into an inventory location.

The other complexity is consumables.  I would add a flag to the Product Table (you do have a product table I hope) to indicate that this product is consumed.  You'll need this when costing what you have on hand.  You'll also need a lifespan field for use in calculating depreciated value.  The IRS has different deprecation schedules for different classes of items.  So, talk to your users about this and find out what they are doing now.  You may need to incorporate it.
0
 

Accepted Solution

by:
OneByte earned 0 total points
ID: 40608006
Thank you for your advice Scott McDaniel and PatHartman! To answer, the LocationID in the Employee and Inventory tables are that of their "home" location.

The solution in my database is simplified for this example and relates only to the original inquiry of tracking inventory of an item/asset and having a reorder section. I did not include the dashboard but that can easily be done with a query/form to display those up for reorder.
Item-Distribution.accdb
0
 

Author Closing Comment

by:OneByte
ID: 40616137
With the advice from an Expert Comment I moved forward designing my tables and creating the applicable relationships. I then researched and built the rest.
0

Featured Post

Threat Intelligence Starter Resources

Integrating threat intelligence can be challenging, and not all companies are ready. These resources can help you build awareness and prepare for defense.

Join & Write a Comment

This article will show you how to use shortcut menus in the Access run-time environment.
Shadow IT is coming out of the shadows as more businesses are choosing cloud-based applications. It is now a multi-cloud world for most organizations. Simultaneously, most businesses have yet to consolidate with one cloud provider or define an offic…
The viewer will learn how to use a discrete random variable to simulate the return on an investment over a period of years, create a Monte Carlo simulation using the discrete random variable, and create a graph to represent the possible returns over…
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…

708 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

18 Experts available now in Live!

Get 1:1 Help Now