Searching for everything that isn't in a query?

[Adam7]

Hi,

I have a database with 1.5million or so records. I have 6 queries that contain approx. 400,000 of these records.
Is there a way I can show the remaining 1.1million records (i.e. everything that is not in one of the queries) without having to copy all of the criteria and putting "is null" next to them?

Thanks in advance!

Adam.

[help_me_with_access]

it depends Mr. Adam...what are you looking for? what does your eye want to see? just numbers that represent the exclusions? or do you want to see entire recs, including every field in those recs?

do you have any idea what size, in bytes, the average record is? I'm speaking about the records that are NOT in the queries you're talking about...

your 2 readily available choices for statements are:

NOT IN (query name), or
NOT EXISTS (query name)

or, plenty of other methods are available as well. for instance, you could write an incredibly small routine in visual basic that can throw all your table data out to excel and you could probably use the interface features of excel to do this filtering procedure faster than access could do it itself!!!

[Adam7]

Thanks for your reply!

The queries at the moment bring up 3 fields- email, category and then a Yes/No field.
I want to see those 3 fields, or the whole record would be ok.

I'm not sure what size the records are sorry, 15 or so fields per record.

Have no experience with VB so would prefer a different method if there is one?

Thanks,
Adam.

[help_me_with_access]

give me the criteria statement first.

is it the same for all 6 qrys?

post them, and I'm pretty sure the complexity of combining all the criteria statements themselves would not overload the jet engine too much.

although it may take a few minutes to run initially...(but that's a guess).


think of the database engine as an air traffic controller doing the job manually. if there are 2 situational levels of approaches that airplanes can take, if the controller can direct plane 1 to a runway without having to get advice from the other plane that's coming into the airport at the same time, that takes far less time than having to communicate with plane 2 for the purposes of making sure they don't collide. but hey, if the controller knows that plane 2 is irrelevant to plane 1, he need only worry about #1!

so the engine's equivalent to that would be something like....if you can pick and choose the factors that affect a single output (result) that comes from one source only, it takes much less time than having to take a cross-transactional approach. e.g. - subqueries / cross-communication channels.

[Adam7]

Thanks again for trying to help but Ive managed to do it myself in a very long winded process with an update query!

Thanks,

Adam