Solved

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

Posted on 2013-05-22
5
640 Views
Last Modified: 2013-05-24
I have a table called JD setup with the columns listed below.

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

Open 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

Open 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;

Open 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	

Open 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

Open 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.
0
Comment
Question by:bignadad
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 3
  • 2
5 Comments
 
LVL 93

Expert Comment

by:Patrick Matthews
ID: 39190521
So i had to use this series of queries to get all my subnumbers on one row.

Instead of fixing your queries, I must ask: why did you do that in the first place?  It appears that your original data was at least somewhat normalized, but your process is denormalizing it...
0
 
LVL 2

Author Comment

by:bignadad
ID: 39190629
When a user searches an OE number i need all those subnumbers to match. So when the OEMPartNumber and OEMSubnumbers(all of them) are found they all compare to OEMItem on AMI Table.

How else could i do it without denormalizing data?
0
 
LVL 93

Accepted Solution

by:
Patrick Matthews earned 500 total points
ID: 39190658
Consider what happens when a user searches on OEMNumber and subnumber.  With your data denormalized, you will have to look in at least nine separate subnumber columns in your JD table.  That is usually an indication of bad design.
0
 
LVL 2

Author Comment

by:bignadad
ID: 39190773
okay, that makes sense to me. How else could i do it?

Just looking to get the done the best way possible. Any suggestions are greatly appreciated
0
 
LVL 2

Author Closing Comment

by:bignadad
ID: 39194865
Thanks, ill figure out what i need to do
0

Featured Post

NFR key for Veeam Agent for Linux

Veeam is happy to provide a free NFR license for one year.  It allows for the non‑production use and valid for five workstations and two servers. Veeam Agent for Linux is a simple backup tool for your Linux installations, both on‑premises and in the public cloud.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This post contains step-by-step instructions for setting up alerting in Percona Monitoring and Management (PMM) using Grafana.
Your data is at risk. Probably more today that at any other time in history. There are simply more people with more access to the Web with bad intentions.
Video by: Steve
Using examples as well as descriptions, step through each of the common simple join types, explaining differences in syntax, differences in expected outputs and showing how the queries run along with the actual outputs based upon a simple set of dem…
In Microsoft Access, learn the trick to repeating sub-report headings at the top of each page. The problem with sub-reports and headings: Add a dummy group to the sub report using the expression =1: Set the “Repeat Section” property of the dummy…

622 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