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

I have a table that contains the following

JD
OEMPartNumber
OEMDescription
OEMSubNumber1
OEMSubNumber2
OEMSubNumber3
OEMSubNumber4

OEMSubNumber2, 3 and 4 are all empty. I need to fill those columns with results from the query i need.

I used the following sql query to find all records where OEMPartNumber and OEMSubNumber Matched
SELECT OEMPartNumber FROM JD WHERE OEMPartNumber = OEMSubNumber
UNION SELECT OEMSubNumber FROM JD;

Open in new window


Here is an example

OEMPartNumber - OEMDescription - OEMSubNumber
AR57112 - USE PN AR82761 - AR82761
AR82761 - USE PN AR88536 - AR88536
AR88536 - Lever

What i need to get that to is

OEMPartNumber - OEMDescription - OEMSubNumber - OEMSubNumber2
AR88536 - Lever - AR82761 - AR57112

The reason for this is because i need to get rid of those duplicate rows that just reference the latest part number.

So any duplicate from that query above needs to be placed in the OEMSubNumber2, 3 or 4 depending on how many.

Make Sense? Is this possibe?
LVL 2
bignadadAsked:
Who is Participating?
 
0DocConnect With a Mentor Commented:
I found the issue.  I tweaked the join on the second query and now it is showing the correct results.  Try This:

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

Results:
OEMPartNumber      OEMDescription      OEMSubNumber      OEMSubNumber2
AR88536                        Lever                       AR82761                        AR57112
0
 
0DocCommented:
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
0
 
bignadadAuthor Commented:
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
0
Improve Your Query Performance Tuning

In this FREE six-day email course, you'll learn from Janis Griffin, Database Performance Evangelist. She'll teach 12 steps that you can use to optimize your queries as much as possible and see measurable results in your work. Get started today!

 
bignadadAuthor Commented:
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
0
 
bignadadAuthor Commented:
"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?
0
 
Jeffrey CoachmanMIS LiasonCommented:
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...
0
 
bignadadAuthor Commented:
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...
0
 
0DocCommented:
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.
0
 
bignadadAuthor Commented:
@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?
0
 
0DocCommented:
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.
0
 
bignadadAuthor Commented:
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*"));

Open in new window


second Query

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

Open in new window

0
 
0DocCommented:
I don't see anything obvious.  Could you attach a sample of your database with the just the table and two queries?
0
 
bignadadAuthor Commented:
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
0
 
bignadadAuthor Commented:
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;

Open in new window

0
 
bignadadAuthor Commented:
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?
0
 
0DocCommented:
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
0
 
bignadadAuthor Commented:
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;

Open 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
0
 
0DocCommented:
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).
0
 
0DocCommented:
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
0
 
bignadadAuthor Commented:
i would like to see
RE524752/ENGINE OVERHAUL KIT/RE501569/RE38556/RE38555/RE16926/AR102278/AR102282
0
 
0DocCommented:
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
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.