One of the things you can do in SQL Server and Oracle (likely other DBMSs I haven't tried also) is make much more use of nested SELECT statements than we are allowed to do in Fox. So you may find that you can build this as one query with multiple nested queries. When I've needed to do things like this in Oracle or SQL Server, I've always been amazed at how far it can be taken. The statements get tougher to read maybe, but formatting can help there.

I know that your example was simplified and, since I don't have your tables to test against, I'm not guaranteeing that this would work, but this should give you the idea of how to apply it to your more-complicated situation. If not, just let us know.

Select Acctnum
 from Checks
 Where checks.type = '1'
Union
Select Acctnum ;
 from Checks ;
 Where Acctnum not in
	(Select Acctnum
	   from Checks
	  Where checks.type = '1')

Again, because the scenario has been simplified, the above query doesn't really make a lot of sense. In this case, we basically have the same query 2x -- once used to get some records and once used to exclude those same records. However, since your example is of a nature where a SELECT DISTICT would have worked in the first place (not meaning to criticize <s>), I think you'll find that when the idea is applied to your real scenario, it makes more sense.

The bottom line is that you can insert SELECT statements in many more places within SQL Server and Oracle commands than you can in FoxPro SQL statements. Plus you can nest SELECTs several levels deep. Those things combine, IMO, to make the intermediate query results that we used so often in FoxPro not necessary. Of course, it's often quite a fun puzzle to come up with the solution, but then I always find joy in getting there.

The temp table route may work for you as well, but realize that they go into one space for all users so you have to be weary of naming collisions (gen a unique name and clean up). Actually, I'm pretty sure there's even a way around that (giving each user his/her own temp space) but I don't recall the details of that route.

>The 1st Table pulls out the Account Number information based
>on the checking of 2 Tables, of about 241 million records...
>The 2nd Query, I want to pull out the Account #'s that are not
>in the First Query..
>
>So I should have asked, how do I Select Records that are not
>in a Select Statement that has just been run and returned
>a result set somewhere...