Using MS Acess SQL Passthrough To AS400 to find wholes in number sequence between to files

1240 pts.
iseries v5r4
iSeries V6R1
Microsoft Access
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

Answer Wiki

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

Yes, with two sql statemetns

If you change the join RDB.CARF1.INMSP100 INMSP100 join RDB.CARF1.MSPMP100 MSPMP100
it will show every record from the field on the left RDB.CARF1.INMSP100 INMSP100
even those that aren’t matched on the right but you want to eliminate those with a match on the righ
so at the end of the sql add

where MSPMP100.CLASS is NULL

That will give you INMSP100’s that aren’t matched

For the others you will need to reverse the process
where INMSP100.MSCLS is NULL


Littlepd asked a good question.
You can run this statement on the 400 – in a session use STRSQL
or using Navigator select SQL
either option would be faster.



Not familiar with “pass-though” in Access — it’s been awhile since I’ve used access (hit computers were made of rocks and had cranks). The pass-through should do just fine. Data Transfer System i is used for coping data from the AS/400 (system i) to your pc. So did the querys with Left and Right join work?


Discuss This Question: 4  Replies

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.
  • Littlepd
    Why is this an Access query passthrough to the 400? Why aren't you just running this SQL on the 400?
    1,130 pointsBadges:
  • CompEng
    I am new in all this..I have found that using the AS400 system is some what overbearing. I have used the AS400 query menus in the past. In do so, I had to go to diferent screens and enter the files in one area then enter the feilds in another section, do the relationships in anothe.... then you need to move to different section to set up your print out or output section. I also have tried the the Iseries and that was some what difficult at the start. There are alot of times that I need to do comparisons of the data and with using the Access it was easer to to the links and comparissions too. I followed your advice and
    1,240 pointsBadges:
  • Dmenke38
    By the way, the pass through query does run on the AS400, that is the whole reason for doing it. Pass through queries in Access are commonly sent to the target DBMS through an ODBC connector. If the target tables are large and the result set is a fraction of the target, this avoids the shipping of the whole target table set over the network, as Access would do if the target tables were merely linked to the Access database. Pass through queries must be in the target DBMS SQL syntax, another clue to it being run in the target server. He is using Access as a convenient front end because it has QBE and a convenient display interface.
    185 pointsBadges:
  • CompEng
    Sorry about the above I hit the wrong key and was not able to proof or continue. What is the best way to pull the info from the AS400 and then be able to manipulate? It seems easier to use Access and do it all in the SQL window? But I looked at the AS400 and found the "Data Transfer System i- PC5250SA.TTO, associated with the AS400 system and there is a "System i". When you click on the Data Option a "Change Data Options” window appears and it lists different input location titles: Group by, Select, Where, Have, and Order by. There are other options "Return records with missing fields or Enable group functions” that can be checked. This looks like the place that you would enter the SQL statement? Yes or No? I would like to learn whatever I can to be able to interact with both the Access and AS400 and manipulate the data and other database systems. So finding way to go about it to interact with all the different systems is my goal. So I am sure we will be talking a lot. You input and direction would be greatly appreciative.
    1,240 pointsBadges:

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.

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


Share this item with your network: