Using SQL with MS Access and the AS400.

1185 pts.
Tags:
MS Access Database Table
SQL
Each of the numbers in the column [New PART] is linked to the column [OLD PART]. The [New Part] column may have multiple rows of the same number in the same column but is it due to the association to the [OLISTd part] column, which are out dated numbers. Both the [OLISTd part] and the [new part] columns need to have the identical information in the [79to5000NewNumberToBeHandOff].
So as long as the info in the [79to5000NewNumberToBeHandOff] . Application] is the same then number in the [OLISTd part] and the [new part] is a usable numbers. What I need to do is compare the numbers in the [OLISTd part] and the [new part] to the {application} if the {application} has multiple different listing then the number that is repeated in either the [OLISTd part] or the [new part] column is a bad number and I can't us it. So how can I have the SQL statement that looks at the number in the [OLISTd part] and the [new part] columns then look at the
SELECT [79to5000NewNumberToBeHandOff].PhantomNo, [79to5000NewNumberToBeHandOff].NewNoVorNot, [79to5000NewNumberToBeHandOff].OEtoPhantom, [79to5000NewNumberToBeHandOff].Application, [79to5000NewNumberToBeHandOff].YrAppl, ChyLastestCalbration10_10_09.[OLD PART] as 'CLC OldPN', ChyLastestCalbration10_10_09.[NEW PART]as 'CLC NewPN', [79to5000NewNumberToBeHandOff].Application, [IN0140 79 wOE].PN as 'NPSpn', [IN0140 79 wOE].OENo as 'NPSoeNo' FROM (79to5000NewNumberToBeHandOff INNER JOIN ChyLastestCalbration10_10_09 ON [79to5000NewNumberToBeHandOff].OEtoPhantom = ChyLastestCalbration10_10_09.[NEW PART]) LEFT JOIN [IN0140 79 wOE] ON ChyLastestCalbration10_10_09.[OLD PART] = [IN0140 79 wOE].ComprsOE;



Software/Hardware used:
MS Access, AS400, MS Office 2000.

Answer Wiki

Thanks. We'll let you know when a new response is added.

I’m sorry but, what is the a question?
Phil

What I have done is developed a little example. Each example is in the same query. Example 1 is one that I can use. The second one is one that I need know of. The case is I need to be able to develop a query that will look at each of the number columns (Column2 & 3). If Column 1 is the same I want to keep the list. if column 1 is made up of different listing I need to receive a list or a query showing these groups. I need to know all the listing that would show column 1 as different and the column 3 would be the same based on the query above. The [ ] show the original fields.

Query ACD
Example 1
<application] [old Part] [New Part]
Column 1 Column2 Column 3
—————————————————————————–
Toy Car 2347AB 8753RT
Toy Car 5432AC 8753RT
Toy Car 6748MR 8753RT

—————————————————————————-
Example 2
Column 1 Column2 Column 3

Toy Car 1588AB 1254ED
Toy Truck 5971AC 1254ED
Toy Sub 1472MR 1254ED

——————————————————————————–

Solving this for your original query .. ohhhhh what a headache but if you materialized that query
by inserting it into a new temorary file..then solving it for this issue seperately …
I’m going to call the data with the examples above myFile and the columns Col1, Col2, Col3
and I’ll join it to itself

Select 1 using <

Select a.Col1, a,col2, a.col3, b.col1, b.col2, b.col3
from myfile.a join myfile.b
on a.col3 = b.col3 and a.col1 < b.col1

a.Col1 a.Col2 aCol3 b.Col1 b.Col2 b.Col3
——————————————————
Toy Car 1588AB 1254ED Toy Truck 5971AC 1254ED
Toy Car 1588AB 1254ED Toy Sub 1472MR 1254ED
Toy Truck 5971AC 1254ED Toy Sub 1472MR 1254ED

or
Select 2 using <>

Select a.Col1, a,col2, a.col3, b.col1, b.col2, b.col3
from myfile.a join myfile.b
on a.col3 = b.col3 and a.col1 <> b.col1

a.Col1 a.Col2 aCol3 b.Col1 b.Col2 b.Col3
——————————————————
Toy Car 1588AB 1254ED Toy Truck 5971AC 1254ED
Toy Car 1588AB 1254ED Toy Sub 1472MR 1254ED
Toy Truck 5971AC 1254ED Toy Car 1588AB 1254ED
Toy Truck 5971AC 1254ED Toy Sub 1472MR 1254ED
Toy Sub 1472MR 1254ED Toy Car 1588AB 1254ED
Toy Sub 1472MR 1254ED Toy Truck 5971AC 1254ED

or
Select 3 Column3 appears more than once.

Select Col1, col2, col3
from myFile
where col3 in
( select col3 from myFile group by col3 having count(*) > 1 )

Col1 Col2 Col3
—————————-
Toy Car 1588AB 1254ED
Toy Truck 5971AC 1254ED
Toy Sub 1472MR 1254ED

Don’t know if any of these help…
Phil

Discuss This Question:  

 
There was an error processing your information. Please try again later.
Thanks. We'll let you know when a new response is added.
Send me notifications when members answer or reply to this question.

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy

Forgot Password

No problem! Submit your e-mail address below. We'll send you an e-mail containing your password.

Your password has been sent to:

To follow this tag...

There was an error processing your information. Please try again later.

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy

Thanks! We'll email you when relevant content is added and updated.

Following