Q:
Using SQL with MS Access and the AS400.
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.
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.
ASKED:
Oct 13 2009 2:16 PM GMT
_ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _
A:
RATE THIS ANSWER
0
Click to Vote:
0
Click to Vote:
- 0
0
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
-----------------------------------------------------------------------------
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
