Need to manipulate data in Access table. Using SQL to bring in data and make delete's

I have a table called JD setup with the columns listed below.

JD
---------------------------
OEMPartNumer | OEMDescription    | OEMSubNumber     | OEMSubNumber2    | OEMSubNumber3    | OEMSubNumber4    | OEMSubNumber5    | OEMSubNumber6    | OEMSubNumber7    | OEMSubNumber8    | OEMSubNumber9

Select allOpen in new window

I had to go through a series of queries to pull out all the sub numbers. The reason for this is because my original table consisted of the following

JD
---------------------------
OEMPartNumer    | OEMSubNumber    | OEMDescription
AR57112   -   USE PN AR82761   -   AR82761
AR82761   -   USE PN AR88536   -   AR88536
AR88536   -   Lever

Select allOpen in new window

So i had to use this series of queries to get all my subnumbers on one row.
This is the step9 query

SELECT DuplicateStep8.OEMPartNumber, DuplicateStep8.OEMDescription, DuplicateStep8.OEMSubNumber, DuplicateStep8.OEMSubNumber2, DuplicateStep8.OEMSubNumber3, DuplicateStep8.OEMSubNumber4, DuplicateStep8.OEMSubNumber5, DuplicateStep8.OEMSubNumber6, DuplicateStep8.OEMSubNumber7, DuplicateStep8.OEMSubNumber8, JD.OEMPartNumber AS OEMSubNumber9
FROM DuplicateStep8 LEFT JOIN JD ON DuplicateStep8.OEMSubNumber8 = JD.OEMSubNumber1;

Select allOpen in new window

So now i want to get all these sub numbers back to my original table JD

Thing is my query was causing duplicates. This is because on of the sub numbers referenced a sub number not inline with the others. Look at the table below for an example.

OEMPartNumber	OEMDescription	OEMSubNumber	OEMSubNumber2	OEMSubNumber3
AFH205783	HOOK	AE48700	AE32450	AE23128						
AFH205783	HOOK	AE48700	AE32450	AE22894	

Select allOpen in new window

     

So if OEMPartNumbers are duplicated i need to compare OEMSubNumbers until they are different then move the different OEMSubNumber to the row to keep in the next available OEMSubNumber and delete the other row where the OEMSubNumber came from so i dont end up with duplicates.

I need this all on one table to users on my website can search OEMPartNumber or any of the OEMSubNumbers. That compares to another table i have shown below

AMI
---------------------------
Item            | OEMItem   | Description
AMAR65123       | AR65123   | Axle
AMAR56242       | AR12345   | Spindle

Select allOpen in new window

     

the OEM Number that is searched is found in the JD Table and all of those numbers on that row are compared to OEMItem and if any match then it shows the user the Item (which is my companies part number)

That is the site i built for users to make their searches.
http://bakerabilene.com/interchange.aspx

Thank you for any help in this matter.