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

Posted on 2014-10-01
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.
Ransomware-A Revenue Bonanza for Service Providers

Ransomware – malware that gets on your customers’ computers, encrypts their data, and extorts a hefty ransom for the decryption keys – is a surging new threat.  The purpose of this eBook is to educate the reader about ransomware attacks.


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

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

NEW Veeam Agent for Microsoft Windows

Backup and recover physical and cloud-based servers and workstations, as well as endpoint devices that belong to remote users. Avoid downtime and data loss quickly and easily for Windows-based physical or public cloud-based workloads!

Question has a verified solution.

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

This article describes how to import an Outlook PST file to Office 365 using a third party product to avoid Microsoft's Azure command line tool, saving you time.
This article describes how to use a set of graphical playing cards to create a Draw Poker game in Excel or VB6.
In Microsoft Access, when working with VBA, learn some techniques for writing readable and easily maintained code.
Excel styles will make formatting consistent and let you apply and change formatting faster. In this tutorial, you'll learn how to use Excel's built-in styles, how to modify styles, and how to create your own. You'll also learn how to use your custo…

695 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