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

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.
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

You can download Northwind database template from MS site:
it could be a good start point for you
Scott McDaniel (Microsoft Access MVP - EE MVE )Infotrakker SoftwareCommented:
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.
OneByteAuthor Commented:
I have seen this and a number of other templates but still am not successful. I have made a few attempts.
Acronis True Image 2019 just released!

Create a reliable backup. Make sure you always have dependable copies of your data so you can restore your entire system or individual files.

OneByteAuthor Commented:
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.
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.
OneByteAuthor Commented:
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.
OneByteAuthor Commented:
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.
Scott McDaniel (Microsoft Access MVP - EE MVE )Infotrakker SoftwareCommented:
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?
OneByteAuthor Commented:
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.
Scott McDaniel (Microsoft Access MVP - EE MVE )Infotrakker SoftwareCommented:
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).
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.
OneByteAuthor Commented:
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.

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
OneByteAuthor Commented:
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.
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Office

From novice to tech pro — start learning today.