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

Posted on 2014-10-01
Medium Priority
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.
Question by:OneByte
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
  • 7
  • 3
  • 2
  • +1
LVL 40

Expert Comment

ID: 40355316
You can download Northwind database template from MS site:
it could be a good start point for you
LVL 85
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.

Author Comment

ID: 40355325
I have seen this and a number of other templates but still am not successful. I have made a few attempts.
Get proactive database performance tuning online

At Percona’s web store you can order full Percona Database Performance Audit in minutes. Find out the health of your database, and how to improve it. Pay online with a credit card. Improve your database performance now!


Author Comment

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

Expert Comment

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.

Author Comment

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.

Author Comment

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.
LVL 85
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?

Author Comment

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

Assisted Solution

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

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).
LVL 38

Expert Comment

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.

Accepted Solution

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.

Author Closing Comment

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.

Featured Post

Will your db performance match your db growth?

In Percona’s white paper “Performance at Scale: Keeping Your Database on Its Toes,” we take a high-level approach to what you need to think about when planning for database scalability.

Question has a verified solution.

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

In this article, I’ll look at how you can use a backup to start a secondary instance for MongoDB.
New style of hardware planning for Microsoft Exchange server.
Learn how to make your own table of contents in Microsoft Word using paragraph styles and the automatic table of contents tool. We'll be using the paragraph styles in Word’s Home toolbar to help you create a table of contents. Type out your initial …
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…
Suggested Courses

762 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