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 show my tables' relationships that I could email you and/or I can email you a copy of my 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.
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 show my tables' relationships that I could email you and/or I can email you a copy of my 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.
