GrandmasterB
New member
- Local time
- Today, 02:19
- Joined
- Jan 26, 2009
- Messages
- 9
Hi All,
I've searched the web, the forums, everywhere, for guidance on this, and am just not understanding how to apply the posts and responses to my problem. Here's my problem:
Background
My small business (an equipment integration company, meaning we use parts from other companies to build larger systems) has been running for many years without a viable inventory control system. Since my takeover, I have been working to assign all of the pieces we have on the shelf with a standard part number. This has gone well, but now the inventory parts number in the low thousands, and we also order parts for specific jobs. Since we respond quickly to orders from our customers and have built our business model around that, we get killed on shipping parts in as 1's and 2's on a job by job basis...if we can look at what we are actually using, we can be better prepared and save $ along the way.
The effort to assign part numbers can be considered "done" for the purposes here, and each part number can function as a unique ID.
Problem
There are multiple ways to name each individual part, and there has never been any standardization. Example:
1" sch80 PVC T soc -- is a 1" diameter, PVC plastic, schedule 80 wall thickness, socket style, tee spool piece.
It also can be written as
1" PVC Tee sch80 s
or
schedule 80 1" soc Tee PVC
...anyone working there can read all of those and know exactly what we are talking about.
If you go back through our historical purchasing database which is part of a larger custom DB with an Access 2010 front end and SQL back end, parts like these have been entered every way possible, and until this past october we never had people entering our own PartNo into the database (that information didn't exist until Oct) along with the text description for ordering. Nobody in the past ever thought of putting a VendorPartNo field into the database either, so it isn't like I can compare these hundreds of thousands of records to some other standard. Vendors re-key this information into their own systems when the purchase orders arrive, so you always have a human brain correcting things...and errors are surprisingly rare.
I can live without correcting the historical data by adding our own PartNumber to the fields in order to do a retroactive inventory management analysis, as we have a couple guys who have been around 20+ years who "know" how much of each part to keep on hand...but people don't live forever, they are going to retire soon, and we are growing like crazy so even their experience is falling short.
What I absolutely need to do is correct this moving forward (and if possible I will take the time to apply it retroactively).
Challenge
I need to come up with code that looks at what the user types into our purchasing entry form field and recommends the best, or even a couple, PartNo from our tblPartsList table. When the user picks the right one, I will then populate the PartNo field, the Description field, and any other info from the table tblPartsList into the Purchasing form, and my entries will be standardized.
Layout (simplified for discussion, not mentioning extra fields)
tblPOs has fields
PO (unique ID)
...and information like VendorID, OrderDate, etc, etc...
tblPOsDetails has
DetailID (unique ID)
PO
Description
PartNo
Qty
Price
tblPartsList has
PartNo (unique ID)
Description (which has been standardized)
Location
.
.
etc
What I'd like is for the user to enter their Description into the frmPOsDetails, and have Access compare that Description to tblPartsList!Description, returning one or a few PartNos. They pick the right one (either by clicking on it or manually keying it in), and frmPOsDetails then populates PartNo and Description from tblPartsList.
My data will be standardized at that point, and we can start generating data to optimize our Inventory management plan. To sum up, right now we really have no idea how many 1" Schedule 80 PVC socket Tees we use in a year, all we know is JoeBob out in the shop thinks we should keep 25 on the shelf, so we do. (This doesn't matter for small, cheap parts, but we stock material that can be large and cost into the thousands of dollars each).
I am self-taught in VBA and Access DB design, and have spent countless hours lurking here and other places and "borrowing" your code to modify and use. Many thanks, by the way. I've hit a wall here, and have no idea how to do the Fuzzy matching routine I think would solve this (and I don't think Soundex would work, but what do I know?).
Is this insurmountable?
...should I be taking classes? (no time)
...is it time to hire a professional??? (not big enough yet to outsource)
I've searched the web, the forums, everywhere, for guidance on this, and am just not understanding how to apply the posts and responses to my problem. Here's my problem:
Background
My small business (an equipment integration company, meaning we use parts from other companies to build larger systems) has been running for many years without a viable inventory control system. Since my takeover, I have been working to assign all of the pieces we have on the shelf with a standard part number. This has gone well, but now the inventory parts number in the low thousands, and we also order parts for specific jobs. Since we respond quickly to orders from our customers and have built our business model around that, we get killed on shipping parts in as 1's and 2's on a job by job basis...if we can look at what we are actually using, we can be better prepared and save $ along the way.
The effort to assign part numbers can be considered "done" for the purposes here, and each part number can function as a unique ID.
Problem
There are multiple ways to name each individual part, and there has never been any standardization. Example:
1" sch80 PVC T soc -- is a 1" diameter, PVC plastic, schedule 80 wall thickness, socket style, tee spool piece.
It also can be written as
1" PVC Tee sch80 s
or
schedule 80 1" soc Tee PVC
...anyone working there can read all of those and know exactly what we are talking about.
If you go back through our historical purchasing database which is part of a larger custom DB with an Access 2010 front end and SQL back end, parts like these have been entered every way possible, and until this past october we never had people entering our own PartNo into the database (that information didn't exist until Oct) along with the text description for ordering. Nobody in the past ever thought of putting a VendorPartNo field into the database either, so it isn't like I can compare these hundreds of thousands of records to some other standard. Vendors re-key this information into their own systems when the purchase orders arrive, so you always have a human brain correcting things...and errors are surprisingly rare.
I can live without correcting the historical data by adding our own PartNumber to the fields in order to do a retroactive inventory management analysis, as we have a couple guys who have been around 20+ years who "know" how much of each part to keep on hand...but people don't live forever, they are going to retire soon, and we are growing like crazy so even their experience is falling short.
What I absolutely need to do is correct this moving forward (and if possible I will take the time to apply it retroactively).
Challenge
I need to come up with code that looks at what the user types into our purchasing entry form field and recommends the best, or even a couple, PartNo from our tblPartsList table. When the user picks the right one, I will then populate the PartNo field, the Description field, and any other info from the table tblPartsList into the Purchasing form, and my entries will be standardized.
Layout (simplified for discussion, not mentioning extra fields)
tblPOs has fields
PO (unique ID)
...and information like VendorID, OrderDate, etc, etc...
tblPOsDetails has
DetailID (unique ID)
PO
Description
PartNo
Qty
Price
tblPartsList has
PartNo (unique ID)
Description (which has been standardized)
Location
.
.
etc
What I'd like is for the user to enter their Description into the frmPOsDetails, and have Access compare that Description to tblPartsList!Description, returning one or a few PartNos. They pick the right one (either by clicking on it or manually keying it in), and frmPOsDetails then populates PartNo and Description from tblPartsList.
My data will be standardized at that point, and we can start generating data to optimize our Inventory management plan. To sum up, right now we really have no idea how many 1" Schedule 80 PVC socket Tees we use in a year, all we know is JoeBob out in the shop thinks we should keep 25 on the shelf, so we do. (This doesn't matter for small, cheap parts, but we stock material that can be large and cost into the thousands of dollars each).
I am self-taught in VBA and Access DB design, and have spent countless hours lurking here and other places and "borrowing" your code to modify and use. Many thanks, by the way. I've hit a wall here, and have no idea how to do the Fuzzy matching routine I think would solve this (and I don't think Soundex would work, but what do I know?).
Is this insurmountable?
...should I be taking classes? (no time)
...is it time to hire a professional??? (not big enough yet to outsource)
Last edited:
