Excluding grouped records where condition is not met

[AlexD]

Hi,

I have a contacts table made up of records which are a sub-group of a larger parent table. There is a one-to-many relationship between the parent record (company) and the records in the contact table. The records are linked thru a record ID number.

The contact table has a Y/N flag for each contact denoting whether they are 'Head of IT'.

eg record ID no. 17 might have 3 contacts, with 2 marked N for Head of IT and 1 marked Y.

I need a query that excludes any group of contacts that have "Y" as 'Head of IT' in any of the contact records. In other words, just leaving the records where no Head of IT has been recorded for that company.

I hope this makes sense.

Any help appreciated,

Alex

 

[Rich]

Put No in the criteria section of the Y/N flag field of your query

 

[AlexD]

If only it was that simple

That would still return a grouped record ID, when one of the contacts had "Y" for [Head_of_IT]

What I'm looking to do is return those grouped records where NONE of the contacts has "Y" in [Head_of_IT].

Maybe giving an example might make it clearer, if I had :

Record Id Head_of_IT
17 Y
17 N
17 N

Then the grouped Record 17 would not be returned as there is a Y present.

but if I had

Record ID Head_of_IT
89 N
89 N
89 N
89 N

Then it should return a grouped result of

89 N

as there is no Head_of_It = Y.

Hopefully that's clarified it a bit.

cheers, Alex

 

[samcoinc]

could you do a min in you grouping query. if your yes/no field is set up normaly then -1 would be a yes, 0 would be a no.

edit

it would then show the yes if there was a yes for that group of numbers - then filter the yeses out


sam

 

[AlexD]

That worked.

It was 'Max' that did it, but thanks for putting me on the right track, Sam.

cheers, Alex