Need to Query Data in Access Database to find matching numbers and put in new column

There may be a more elegant solutions, but this should get you what started.  First create a query for the non-substitute parts and then create a series of queries for each level of part substitution.

Here is some sample code.  I used table name of JD and the first query I called JDFirstSubstitution.  The names might not make sense, but hopefully the code will help get you what you need.

JDFirstSubstitution          (list non-substitute parts with first substitution)
SELECT JD.OEMPartNumber, JD.OEMDescription, JD_1.OEMPartNumber AS OEMSubNumber
FROM JD LEFT JOIN JD AS JD_1 ON JD.OEMPartNumber = JD_1.OEMSubNumber1
WHERE (((JD.OEMDescription) Not Like "USE PN*"));

Results:
OEMPartNumber      OEMDescription      OEMSubNumber
AR88536                        Lever                         AR82761

JDSecondSubstitution     (adds parts that are the first substitution)
SELECT [JDFirstSubstitution].OEMPartNumber, [JDFirstSubstitution].OEMDescription, [JDFirstSubstitution].OEMSubNumber, JD.OEMSubNumber1
FROM JDFirstSubstitution LEFT JOIN JD ON [JDFirstSubstitution].OEMSubNumber=JD.OEMPartNumber;

Results
OEMPartNumber      OEMDescription      OEMSubNumber      OEMSubNumber1
AR88536                       Lever                       AR82761                        AR88536

You can build more levels of substitutions with additional queries.

To finish up, create an insert query to insert the sub part numbers.  However, inserting the data will likely create issues down the road as new part number and new substitutions happen - it becomes tricky keeping the table up to date.  I recommend not actually inserting the data, but leaving it as a query.

I hope that helps,

0doc

Thank you so much for the response.

The first query seems to work fine.

But the 2nd query has NO results for OEMSubNumber or OEMSubNumber1


This is my table layout
JD
OEMPartNumber
OEMDescription
OEMSubNumber1
OEMSubNumber2
OEMSubNumber3
OEMSubNumber4

Sorry, it sorted those results at the bottom. Let me keep working and see if i can get this completed.

Thanks again, will be back in a moment

"However, inserting the data will likely create issues down the road as new part number and new substitutions happen - it becomes tricky keeping the table up to date.  I recommend not actually inserting the data, but leaving it as a query."

That makes good sense. I will probably do that.

How would the additional query be setup for the next set of sub numbers?

Note that your whole issue here is rooted in the fact that your table is not "Normalized"
(has repeating fields)

If this was normalized, (two related tables), then this all would be easy...

What can I do to normalize it?

I am trying to clean this table of 750,000 part numbers up so it gives back the correct information on my sql database.

http://bakerabilene.com/interchange.aspx

That is where i will use this data. Basically a user inputs a OEM Number (whether it be the main or sub1, 2 etc) and it matches any of those OEM numbers to my Abilene Machine part number.

That is the whole point of this. Based on that can i change anything to make this work better? I have 5 more brands to do this with, IH, AGCO, NH, MF etc...

In response to "But the 2nd query has NO results for OEMSubNumber or OEMSubNumber1", Ensure the link is an left join from JDFisrtSubstitution.OEMSubNumber to JD.OEMPArtNumber.

@0Doc

You seem to be on to something here but i can't seem to wrap my head around how to do it.

the following results
Results
OEMPartNumber      OEMDescription      OEMSubNumber      OEMSubNumber1
AR88536                       Lever                       AR82761                        AR88536

OEMSubNumber1 is the same as OEMPartNumber. It should be the AR57112.
I do see that the first query DID retrieve the OEMSubNumber and thats good. But im confused how to get the next one.

The is the example im looking at from my table

OEMPartNumber      OEMDescription      OEMSubNumber1      OEMSubNumber2
AR57112                      USE PN AR82761          AR82761                  
AR82761                      USE PN AR88536          AR88536                  
AR88536                        LEVER                        

So your first query got the AR82761 to AR88536's Sub Number. But how do i get AR57112 to AR88536's OEMSubNumber2?

The issue is with the link in your second query.  If you want, send me your first and second query SQL code and I should be able to spot the problem.

First query

SELECT JD.OEMPartNumber, JD.OEMDescription, JD_1.OEMPartNumber AS OEMSubNumber
FROM JD LEFT JOIN JD AS JD_1 ON JD.OEMPartNumber = JD_1.OEMSubNumber1
WHERE (((JD.OEMDescription) Not Like "USE PN*"));

Select allOpen in new window

second Query

SELECT [DuplicateStep1].OEMPartNumber, [DuplicateStep1].OEMDescription, [DuplicateStep1].OEMSubNumber, JD.OEMSubNumber1
FROM DuplicateStep1 LEFT JOIN JD ON [DuplicateStep1].OEMSubNumber=JD.OEMPartNumber;

Select allOpen in new window

I don't see anything obvious.  Could you attach a sample of your database with the just the table and two queries?

Im still getting this result.

OEMPartNumber      OEMDescription      OEMSubNumber      OEMSubNumber2
AR88536                       LEVER                       AR82761                        AR88536


attached a test

again thank you for taking the time to help me
test.accdb

Finally got the right query. Just had to swap some things.

Thanks again!

SELECT DuplicateStep1.OEMPartNumber, DuplicateStep1.OEMDescription, DuplicateStep1.OEMSubNumber, JD.OEMPartNumber AS OEMSubNumber2
FROM DuplicateStep1 LEFT JOIN JD ON DuplicateStep1.OEMSubNumber = JD.OEMSubNumber1;

Select allOpen in new window

What would my query look like to get the 2 sub numbers back into the JD table and delete the rows that contain the sub numbers in OEMPartNumber?

I would suggest not getting them back in, but use the query basically like the fully loaded table.  However, if you have to insert them back, use an Insert query.  Be sure to do a backup the bale first and I would recommend trying this first on a test database.  Here is the syntax:

INSERT INTO target [(field1[, field2[, …]])] [IN externaldatabase]     SELECT [source.]field1[, field2[, …]     FROM tableexpression

k, thanks. i will look at both options.

One more thing if you dont mind and i will accept your answer as solution.

This is the sequence im using to get all the sub numbers out

SELECT DuplicateStep4.OEMPartNumber, DuplicateStep4.OEMDescription, DuplicateStep4.OEMSubNumber, DuplicateStep4.OEMSubNumber2, DuplicateStep4.OEMSubNumber3, DuplicateStep4.OEMSubNumber4, JD.OEMPartNumber AS OEMSubNumber5
FROM DuplicateStep4 LEFT JOIN JD ON DuplicateStep4.OEMSubNumber4 = JD.OEMSubNumber1;

Select allOpen in new window

is that right? Im noticing some of the number are duplicated like the one below. Is that because i have not pulled all the subs out OR do i have an error?

OEMPartNumber/OEMDescription/OEMSubNumber/OEMSubNumber2/OEMSubNumber3/OEMSubNumber4/OEMSubNumber5
RE524752/ENGINE OVERHAUL KIT/RE501569/RE38556/RE38555/RE16926/AR102278
RE524752/ENGINE OVERHAUL KIT/RE501569/RE38556/RE38555/RE16926/AR102282

It looks like the duplicate is coming from the data.  Check to see if RE16926 has two substitue parts AR102278 (from the first line) and AR102282 (from the second line).

As a followup, what do you need the queries to show in that situation?
RE524752/ENGINE OVERHAUL KIT/RE501569/RE38556/RE38555/RE16926/AR102278
RE524752/ENGINE OVERHAUL KIT/RE501569/RE38556/RE38555/RE16926/AR102282

i would like to see
RE524752/ENGINE OVERHAUL KIT/RE501569/RE38556/RE38555/RE16926/AR102278/AR102282

The nuance that I wasn't aware of is that there can be more than one OEMPart replaced by the same OEMSub.  That is what is causing the duplication.  The only way I can think of to handle this is to create a VB script to extract the substitute parts and put the results into a new table.  Unfortunately, I do not have the time to volunteer to do that.  Sorry,

0doc