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
  • 7
  • 3
  • 2
  • +1
LVL 39

Expert Comment

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

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.
Netscaler Common Configuration How To guides

If you use NetScaler you will want to see these guides. The NetScaler How To Guides show administrators how to get NetScaler up and configured by providing instructions for common scenarios and some not so common ones.


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 35

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

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

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 35

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

Three Reasons Why Backup is Strategic

Backup is strategic to your business because your data is strategic to your business. Without backup, your business will fail. This white paper explains why it is vital for you to design and immediately execute a backup strategy to protect 100 percent of your data.

Question has a verified solution.

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

Using SQL Scripts we can save all the SQL queries as files that we use very frequently on our database later point of time. This is one of the feature present under SQL Workshop in Oracle Application Express.
This article descibes how to create a connection between Excel and SAP and how to move data from Excel to SAP or the other way around.
The view will learn how to download and install SIMTOOLS and FORMLIST into Excel, how to use SIMTOOLS to generate a Monte Carlo simulation of 30 sales calls, and how to calculate the conditional probability based on the results of the Monte Carlo …
The viewer will learn how to create two correlated normally distributed random variables in Excel, use a normal distribution to simulate the return on different levels of investment in each of the two funds over a period of ten years, and, create a …

772 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