First off I know this topic has been discussed by me a couple times on this site. I am here now to explain in detail what I have and what I need. Any advice is greatly appreciated.
A little background on my company
My company sells new aftermarket parts for tractors, combines and other types of farm equipment. You can check our website out below.
We have 3 stores in Kansas, South Carolina, and California.
Our parts that we sell have a unique part number attached to them. We call it our AMI part number. (AMI = Abilene Machine Inc). We have over 50,000 numbers that we catalog and stock in our inventory.
In most cases we try and take the OEM part number and add a "AM" to the beginning of it to make our AMI part number. In some cases this is not possible though.
The process now
Many times we get orders from different people/companies that give us a list of part numbers and they want to know if we have them available. As it is right now each part number has to be individually researched to see if it matches a part in our catalog. The reason for the research is because of OEM sub numbers. OEM is the original equipment manufacturer, ie John Deere, Case IH, etc. Here is a little info on sub numbers
OEM Sub Numbers Explained
When the OEM develops parts they assign an OEM part number to it. Down the road, could be a month/year/etc, the part design might change just a bit. The OEM then issues a new part design and give it a "new" OEM part number which is actually a sub number.
BUT, that does not mean the original part number will not work anymore. Both original and sub numbers will both work for the application they are used on. If something changes that makes the part not work the number won't sub OR it will require other parts to make it work. But I don't want to get focused on those details because for this question i am only wanting you to know that OEM part numbers and OEM sub numbers are the same part.
Sometimes an OEM part number can have multiple sub numbers. I have seen some have up to 8.
The process now (continued....)
So you see since those sub numbers are present it takes research to see if any of the OEM part/sub numbers matches our numbers. The person doing the research has to go into the OEM catalog searching the part number provided and see if that number subs (or is a sub) then check all those part/sub numbers against our AMI part numbers.
This would also be beneficial for other tasks not mentioned here.
What I am needing done
I figured the solution to this problem was to create a web page that users could go on and easily find if our AMI part number matches any of the OEMpart/sub numbers.
This is what i came up with
That offers the users 2 ways to do their research. They can input one OEMpart/sub number at a time OR they can upload a list of OEM part/sub numbers via a csv file.
Problem is that when i was doing research on getting everything setup the way it is now I was being told that my data was not normalized and I needed to do that before i could have everything function properly. So I stopped with development and looked at my data and that is where I am stuck.
The steps I have done so far
These tables were pulled from an OEM catalog and was the only way we could get the data out. Below is an example of my JD table (all other OEM tables are setup this way)
OEMPartNumer | OEMDescription | OEMSubNumber
AR57112 - USE PN AR82761 - AR82761
AR82761 - USE PN AR88536 - AR88536
AR88536 - Lever
And here is my AMI part number table that has a matching OEM part number
Item | OEMItem | Description
AMAR65123 | AR65123 | Axle
AMAR88536 | AR88536 | Lever
So the problem is that all the OEM part/sub numbers have to be matched against the [OEMItem] from the [AMI table]
Example - If i was to search OEM number AR57112 it would not find it. Because AR57112 doesn't associate with AR88536.
If I was to search AR82761 OEM number that would work because it associates with AR88536 which it matched on OEM Item.
So that is when i figured out that having table in that format would not work. So i tired to do a 9 step query in access to match all the oem part/sub numbers and put them on one line. It kind of worked but left me with many duplicates and I was still being told the data was not normalized and need to be.
I was able to actually get the table working that i did the 9 step query on by using a cross apply sql statment. That was only one table. below you see how much data im working with.
How much data i'm working with
Right now i have the following 3 OEM tables. Agco, JD, Case New Holland. Each have around 1 million records in them. I also have my AMI table that has 50,000 records.
What to do?
I got the ASPX page built and functions fine. I have my SQL server working. All i'm lacking is my data.
So how can i get my data normalized? I assume there has to be a faster way than going down each record one by one.
I got some advice along the way about normalization and it stated i should have 1 unique field and everything else tie to it. So would the unique field be OEM Part Number? It would contain all unique part number whether they are original or sub? then in another column I would have [Original number]. That would specify the original number for the sub number?
Im just not familiar enough with data to make an expert decision. That is why im reaching out to experts here to know how to proceed.
If the tables do have to have extensive work done is this something i should contract out to an online contractor?
Maybe I have went about this all wrong? That is why im providing what i need done and the data i have been given.
Any advice is greatly appreciated. If I can just get my data right this would work great for my company. I hope that i have provided enough information to give experts a good understanding of my situation. Thank you for any replies. I will award points to any good advice.
The ultimate goal is to have a user input a single OEM part/sub number OR a list of OEM part/sub numbers and have it match to the OEM number(whether is the original or sub number) on my AMI table and show the AMI part number.