Exclude Record Set

[Vittles]

I have a Main table that is linked in a one to many relationship to another table where users store 0-6 different field values per one main record.

I want to exclude those main records that have a specific value entered in the many side linked table. For example if John Smith has value #2 (by itself or with other value records) in the linked table, then I do not want to pull John Smith's information.

So far I can query out those linked table records that list the specific value, but not the whole main table record if this value is listed in any of the linked fields.

I have played with grouping, etc. Any ideas?

 

[lespaul]

The following query returns all the records in the Main Table where there is NO entry in the ManyTable:

SELECT * FROM MainTable WHERE PKField NOT IN (SELECT DISTINCT MainTablePKField FROM ManyTable)

Leslie

Anything worth doing is a lot more difficult than it's worth - Unknown Induhvidual

 

[Vittles]

For the following table/field info:

Main Table: "CTU Info" which is linked to the other table via the [CTU ID] field (primary key)
Linked Table:"CTU LN" with specific values saved in the [LN] field.

There is no field in the main table that these LN values are stored - the tables are only linked via ID fields...

The select statement would be (?):
SELECT <all fields needed in query>
From CTU Info
Where CTU ID NOT IN (SELECT DISTINCT CTU Info.CTU ID From CTU LN)

Is this close?

 

[PHV]

Another way:
SELECT * FROM MainTable
WHERE NOT EXISTS (SELECT * FROM ManyTable WHERE ManyTable.FKfield=MainTable.PKfield AND ManyTable.Value='#2')

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[Vittles]

Thanks that is an idea. Now I have add a different layer...

How can I do this with the idea that I don't want Value #2 to show up in either of 2 main tables' (CTU Info & CRS Info (these are linked to each other as well)) their related linked tables (CTU LN & CRS LN). So in other words I want to exclude the records if #2 shows up in the linked LN table for either CTU Info or CRS Info. I was thinking I would do 2 separate queries, but that would only capture those that didn't have '#2' in each - not both.
Relationship structure looks like: [CTU Info]->[CRS Info]
\ \
[CTU LN] [CRS LN]

The other idea I had was to pull those main record(ID) numbers (from CTU & CRS) that actually did have the value and then do a query that said something to the effect of CTU Info.CTU ID <>subquery.CTU ID and CRS Info.CRS ID<>subquery.CRS ID - but I wasn't sure if there was a way to base one query off of another query like that....

I seem to work very well in access until someone wants something that is way off the norm like this... =)