A query that excludes certain records based on the existence of other records...

[spsoisson]

i have a table that was sent to me imported from Excel (tblAllSpec). The field on which my criteria is based is called "SpecType". The table will be integrated into my database eventually, but i have to pull some numbers out before that happens. The field that will eventually be the Primary Key in the parent table is "SBNumber". SO, there are a bunch of records with duplicate SBNumbers with many different SpecTypes.



What i need to determine is what SBNumbers have records WHERE SpecType = 'Type1' and NO records WHERE SpecType = 'Type2'. This seems like such a simple thing but I can't seem to get anything to work.

Thanks! I am relatively new to this and still feeling my way around...

[amrut]

In the Query , use the SpecType field in two columns and enter criteria as =Type1 for one and <>Type2 for the other.
Does this solves you purpose ?

[jzwp11]

I think you will need a query with a subquery

SELECT SBNumber
FROM tablename
WHERE spectype="Type 1" and SBNumber NOT IN (SELECT SBNumber From tablename WHERE spectype="Type 2")

[spsoisson]

i thought it might be an EXISTS/NOT EXISTS issue.

i tried this:

SELECT DISTINCT A.SBNumber
FROM tblAllSpec as A
WHERE (A.SpecType = 'Type1') AND NOT EXISTS (Select SBNumber from tblAllSpec as B Where B.SBNumber = A.SBNumber
and B.SpecType = 'Type2');

this returns results >0 and <total number of records in the table, so it's at least doing something ;-)

@jzwp11, seems we were thinking similar things. @amrut, i believe that what i did was a roundabout way of accomplishing what you suggested.

Thanks so much, guys!

[jzwp11]

You're welcome.