Find record that only matches certain values 1

[rilkyn]

I have a table which has multiple columns but I am only interested in querying on two of them the two columns are structured so:

Column A Column B

CD010 CDU
CD010
CD010 FSA
CD020 CDU
CD030
CD040 CDU
CD040
CD050 FSA

I want to return records from column A which only match either a null vale or CDU in column b
So in the above example I would get back CD020, CD030, CD040 only.

I wrote a query to select records where column B = CDU or Null and another where column B does not = CDU or null but when I try to run a query to find records in query 1 not in query 2 Access just hangs

I'm hoping there is a more efficient way

 

[genomon]

Try this in SQL view:
SELECT ColumnA FROM YourTableName
WHERE (ColumnB IS Null OR ColumnB = 'CDU')

(Those are single quotes around the literal)

Outside of a dog, a book is man's best friend. Inside of a dog it's too dark to read.

 

[traingamer]

One way:

SELECT tblTest1.Cola, Sum(IIf([colb] Is Null Or [colb]="CDU",0,1)) AS sel
FROM tblTest1
GROUP BY tblTest1.Cola
HAVING (((Sum(IIf([colb] Is Null Or [colb]="CDU",0,1)))=0));

(with your data) returns:
Cola sel
CD020 0
CD030 0
CD040 0

Greg
"Personally, I am always ready to learn, although I do not always like being taught." - Winston Churchill

 

[rilkyn]

Greg

Thanks

Your SQL works perfectly. I've been able to modify it to m give me the results I was looking for, though I don't really understand what this bit does:
Sum(IIf([colb] Is Null Or [colb]="CDU",0,1))

If you can point me in the direction of an explanation I'd be greatful.

 

[traingamer]

[COLOR=purple]Sum[/color][COLOR=green](IIf([colb] Is Null Or [colb]="CDU"[/color],[COLOR=red]0[/color],[COLOR=blue]1[/color])) AS sel

This uses the iif (immediate if) function to check 2 criteria. If your field (cola in this example) is Null or if it is equal to "CDU" then assign a value of zero to our calculated field 'sel' - otherwise assign a value of 1. Then use the sum function (used with the grouping clause) to add them all up. If the result is greater than 0, this doesn't meet your criteria.


Greg
"Personally, I am always ready to learn, although I do not always like being taught." - Winston Churchill