Below is a SQL statement from an Ms-access pass-through query that is pulling the info from an AS400 system. The Fields marked in bold are the two table that I need help with. This query returns the same part numbers form each of the two columns of cores because there are of differ tables. I know from running queries of each of these tables separately that there are some numbers that are in the INMSP100.MSPRD that are not in the MSPMP100.PRDNO and vise-a-versa, and there are those numbers that are common to both. I need a list (or a SQL statement) that would show me the Is there anyway to set up this query that it would show a blank in the MSPMP100.PRDNO if the the number is in the INMSP100.MSPRD and the reverse too? I need to know what numbers are in the one but not in the other for both. Or in other wards I need to know the Holes in each of the different columns. Also: A second thought is if there anyway that I could have the query look at each of the numbers columns and show me a hole or a blank if the number sequence is missing a number or a series of them?
Select [strong]INMSP100.MSPRD[/strong] [em]as "IN0140"[/em] ,[strong]MSPMP100.PRDNO[/strong] [em]as "MS0802",[/em]INMSP100.MSCLS,INMSP100.MSOEM,INMSP100.MSOM#,INMSP100.MSOM2,INMSP100.MSOM3,INMSP100.MSOM4,INMSP100.MSOM5,INMSP100.MSOM6, MSPMP100.pmcaor as "MDT Ord",MSPMP100.CLASS From RDB.CARF1.INMSP100 INMSP100 join RDB.CARF1.MSPMP100 MSPMP100 on INMSP100.MSCLS=MSPMP100.CLASS and INMSP100.MSPRD= MSPMP100.PRDNO
August 13, 2009 5:17 PM
August 14, 2009 4:29 PM