WARNING: LONG POST
I know there have been plenty of discussion over inventory control but I have a situation with some additional variables and I need some heads to bounce ideas off of.

I work for a custom cabinet manufacturer and I have a database that I developed that we’ve been in use since 2001. There are several different components to the database, but the last one to be completed (and the one I’m working on now) is job costing.

PROBLEMS/CONSIDERATIONS
Problem #1: Since we are a manufacturing company we use many different materials to make any one product. So far I have all items ordered (since 2001 anyway) in the database but to date I don’t have these items allocated to any particular project – hence the need for what I’m talking about here. One of the unique problems I’m having is the fact that some items are ordered for a particular job (I.E. you know what job they are for at the time of ordering) and some items are “stock” items, meaning that we always keep a certain number of units on hand.

Problem #2: Another unique problem (relating mainly to “stock” items) is some products we use are ordered from different vendors (depending on availability, price, ect.) These products are essentially identical and need to be grouped with other like products at the time of assigning materials to a particular job. On the surface this problem sounds like an easy fix just by grouping the same product name across all vendors. However, it’s not quite that easy because we have many different stock items and product ids are different from vendor to vendor (and I always tell users to use a vendors identification for the product ID and they can use the product description to clearly identify it for our users). Another problem is I have users that are not disciplined enough to keep track of how they named a particular product previous ordered from a different vendor. Therefore I’m convinced I need another method of identifying like products across vendors.

Problem #3: Lastly, I’m still trying to figure out if I should subtract current stock or calculate it anytime “on hand” numbers are needed. Any thoughts on the best method (keeping in mind the variables listed above) would be greatly appreciated.

MY SOLUTION IDEAS
Concerning Problem #1: I want to allocated materials to a job at the time a work ticket is written (this would be the most logical input location since any given work ticket deals with a fairly small portion of an overall job and a material summary at that point is typically already given by another program or would be easy to figure by the person writing the ticket). I would like to do a material input form that would list only materials that have been ordered for the job and then either call another form for stock materials or have a switch to show all stock materials. What I’m unsure of: will I run into problems selecting both from the same form (since they’ll most likely run from different queries) and how will I pull stock items from multiple vendors (i.e. I used 10 sheets of particle board: We have 5 sheets left from vendor #1 and 7 sheets left from vendor #2). – Any thoughts on better methods and/or ways to improve database structure?

Concerning Problem #2: My main thought on combining like products from different vendors without requiring that the product ID match exactly is to enable a “Universal Product Code” anytime a product is tagged as a “stock” item. In the product entry form this would be a combo-box so the user has to select from a list. – With the amount of data I have so far I think I could create a fairly comprehensive list and furthermore my hope would be that if a user had to go to the extra step of adding an item to the list – he/she might think a little harder about properly coding it. – Thoughts/suggestions?

Concerning Problem #3: Given the considerations previously listed, I would ultimately like the database to accurately track inventory so when I order a laminate (or anything else) for a job and we had some left over from a previous job I could see that at the time of ordering. This would also save us time while entering work orders because we could see at the time of release that we are short of materials instead of waiting for the ticket to get into production. Whether I use a subtraction method or calculation method, I still need to figure out how to allocated or indicate which work order the item was designated to. Any thoughts?

I have a PDF file showing my table relationships and/or I can mail you a copy of the database if you'd like to take a look at it.

I apologize in advance for the naming conventions used in my database. This was my first database and some of my earlier work was done without the foresight of proper naming.

Hi,

   I do inventory control in an Access database that I developed and some of these issues sound familiar to what I have done. Here are some suggestions:

Problem #1: Are you trying to track the materials you use for a particular "project" (is this a discrete item being manufactured, or a group of items?) If a discrete item (like a desk, or a discrete project like 100 desks):
You need to do a junction table between the project, and the inventory. Create a table called something like "tblProjectJunction", and have a look up
to your Project name Table, and a look up to your Inventory type table. I am not sure if using duplicate inventory items are a problem. If so, do this:
To assign inventory items to a project, create a single form for your junction table and remove the record selectors. Have these items on the form:
1. A look up to find the inventory item by a discrete characteristic (Ex: serial number or lot)
2. Your current owner field - it should start as "Stockroom" or something like that and be a pull down since it looks up your possible "Owners" or Projects.
Look Up the item, and then change the owner's name with the pull-down. This will allow you to assign an item to an owner without duplicating the same item to different projects.

Whenever you get a new item in, you need to enter in to the database as belonging to the Stockroom or other location.

Problem #2: This is related to the first Problem. You should have the following tables:
A table that keeps track of the types of inventory items by distinct name (EX: FPD 15" NEC, FPD 15" ViewSonic, FPD 17" NEC.)
A table that keeps track of all of your vendors by name (Ex: CDW-G, TigerDirect, etc.)
A table that keeps track of all of your projects by name (Ex: Stockroom, User1, User2, etc.)
A junction table that links these other three. This table should allow you to attach distinct data to each item.
Ex: tblJunction Row 1, from tblAsset - FPD 15" NEC, from tblVendor - CDW-G, from tblOwner - Stockroom, Field SerialNumber (Value A1Z2300045.)
Using the main form, I can see that in the Owner Stockroom, I have a 15" NEC monitor whose serial number is A1Z2300045 that I bought from CDW-G. I could also add a price field if I wanted. I make a seperate entry for each item by serial number, so if I bought this monitor in a lot of 10, I would make 10 entries by serial number. You may make entries by bd/ft for example.

IMPORTANT: the "Project" is also the "Owner." One of the Owners of your Inventory is the Stockroom.
Using a form that looks up the Project Table, and then a subform that shows the junction table, will show you what is in your Stockroom. Using the form from Problem #1 allows you to reassign the owner from the Stockroom to a Project.
Then Using the first form again, you can look up each project and see what inventory items have been assigned to that project.

Problem #3: Perhaps you need an owner called Scrap, with a Notes section to show the Project that used the bulk of an order, or another look up to the previous owner. Scrap could be listed on your Existing Inventory Report.

Please let me know if you would like to see more examples.


mmcdonal