Solved

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

Posted on 2013-05-22
21
447 Views
Last Modified: 2013-05-23
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?
0
Comment
Question by:bignadad
  • 11
  • 9
21 Comments
 
LVL 3

Expert Comment

by:0Doc
ID: 39188105
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
 
LVL 2

Author Comment

by:bignadad
ID: 39188203
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
 
LVL 2

Author Comment

by:bignadad
ID: 39188210
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
 
LVL 2

Author Comment

by:bignadad
ID: 39188222
"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
 
LVL 74

Expert Comment

by:Jeffrey Coachman
ID: 39188237
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
 
LVL 2

Author Comment

by:bignadad
ID: 39188257
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
 
LVL 3

Expert Comment

by:0Doc
ID: 39188356
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
 
LVL 2

Author Comment

by:bignadad
ID: 39188989
@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
 
LVL 3

Expert Comment

by:0Doc
ID: 39189121
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
 
LVL 2

Author Comment

by:bignadad
ID: 39189145
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
Top 6 Sources for Identifying Threat Actor TTPs

Understanding your enemy is essential. These six sources will help you identify the most popular threat actor tactics, techniques, and procedures (TTPs).

 
LVL 3

Expert Comment

by:0Doc
ID: 39189332
I don't see anything obvious.  Could you attach a sample of your database with the just the table and two queries?
0
 
LVL 3

Accepted Solution

by:
0Doc earned 500 total points
ID: 39189362
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
 
LVL 2

Author Comment

by:bignadad
ID: 39189542
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
 
LVL 2

Author Comment

by:bignadad
ID: 39189593
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
 
LVL 2

Author Comment

by:bignadad
ID: 39189595
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
 
LVL 3

Expert Comment

by:0Doc
ID: 39189611
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
 
LVL 2

Author Comment

by:bignadad
ID: 39189620
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
 
LVL 3

Expert Comment

by:0Doc
ID: 39190541
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
 
LVL 3

Expert Comment

by:0Doc
ID: 39190563
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
 
LVL 2

Author Comment

by:bignadad
ID: 39190621
i would like to see
RE524752/ENGINE OVERHAUL KIT/RE501569/RE38556/RE38555/RE16926/AR102278/AR102282
0
 
LVL 3

Expert Comment

by:0Doc
ID: 39191584
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

Featured Post

Complete VMware vSphere® ESX(i) & Hyper-V Backup

Capture your entire system, including the host, with patented disk imaging integrated with VMware VADP / Microsoft VSS and RCT. RTOs is as low as 15 seconds with Acronis Active Restore™. You can enjoy unlimited P2V/V2V migrations from any source (even from a different hypervisor)

Join & Write a Comment

Entering time in Microsoft Access can be difficult. An input mask often bothers users more than helping them and won't catch all typing errors. This article shows how to create a textbox for 24-hour time input with full validation politely catching …
This article explains all about SQL Server Piecemeal Restore with examples in step by step manner.
Familiarize people with the process of utilizing SQL Server views from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Access…
Familiarize people with the process of utilizing SQL Server functions from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Ac…

708 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

17 Experts available now in Live!

Get 1:1 Help Now