An Excluding Query (Any Version) – REVISITED

Topic

New Reply

I can think of two ways offhand.

One is to create a separate query that includes everyone with an A, B or C. Then join that query with an outer join to only include those where the link is Null between the tables.

The other is to create a subquery in your main query using the same select as you would for the first option but only returning the PersonID (or whatever) key. Then you could use Not In with the subquery expression in parens in the criteria for the PersonID field.

Reply | Quote

Replies

Greetings All,

How do you handle making an Excluding query?

Due to the nature of the program my table of data is coming from, it’s a flat file, so the Customer ID and Name Repeat for each record they have in the table, and it is a single table of data no child tables.

Let’s say Eileen has 5 records, but if any of her records has a value of A, B, or C in a particular field I want to exclude her name from the results, while let’s say Charlotte has 9 records and no code of A, B, or C in the same field, so I want to include her. What do you all do to get your results?

I hope I asked this clearly,

TIA

Reply | Quote

This is a sticky one. I’d guess you would have to have a subquery that counted all the invalid records for a particular person, then accepted that person only if the count was zero. Something like:

Select count(*) from Trans Where Trans.custid=cust.custid
AND trans.type in (‘A’, ‘B’, ‘C’)

Reply | Quote

Where are you putting

Select count(*) from Trans Where Trans.custid=cust.custid
AND trans.type in (‘A’, ‘B’, ‘C’)

-TIA

Reply | Quote

I’m putting it as an expression in the “Field” row of the QBE grid. I would then set it selection criteria to =0. This should exclude any customer who has at least 1 transaction containing the verboten values.

Reply | Quote

Select count(YourTable.PersonID) from YourTable
Where YourTable.PersonID not in
(Select subTable.PersonID
from YourTable subTable
where subTable.AlphaField in (‘A’, ‘B’, ‘C’))

Reply | Quote

Doesn’t this only give me a count?

Reply | Quote

OK, Got it!

Charlotte was right there with the use of IN and NOT all I had to do was Group it.

SELECT myTable.myField
FROM myTable
GROUP BY myTable.myField
HAVING (((myTable.myField) NOT IN (SELECT myField From myTable
WHERE theFieldToTest IN(“A”,”B”,”C”))));

This worked very well. This solution was fast too! Compared to my original solution, which took quite a while.

My original method of creating a calculated field with a value of true if in A,B, or C then setting the query to Unique values, took a long long time.

Thanks Charlotte, that was the trick, I forgot about IN and the possibilities! Thanks to all who posted.

As always, if anyone sees a major flaw in my thinking here, please post me in the right direction.

Reply | Quote