exclusionary query trouble (1 Viewer)

[David R]

Trying to create this thing is tying my brain in knots, so I'm back for help. Hopefully I'm missing something basic:

I've finally been able to normalize my database by taking it offline for a few weeks, and already the amount of duplicative/erroneous data that has been cleaned up is staggering. Right now I'm trying to ensure that our data is as complete as possible in the following circumstance:

• tableGroups has PK GroupID
• tablePeople has PK PersonID
• tableRelationships creates a many-to-many relationship between the two, via PersonIDLookup and GroupIDLookup. It also has its own Autonumber PK.
• tableRelationships also has fields that describe the relationship: For example, FormerRelationship, MailingContact, and PublicContact are all boolean fields.

What I want to do is get a list of all GroupIDs which do not have any entry that is marked PublicContact. Problem is that some may have Former contacts, or privately listed ones for internal office use only; over time our Public Contacts move, die, quit, etc and sometimes they have not gotten replaced. I'll need to do the same thing for MailingContact as well, but the procedure for one should be the same as the other. No one who is a Former contact should also be listed as Public/Mailing, but I can't promise the data is that clean at this point.

The only thought that occurs to me right now is to make one query that shows GroupIDs that do have at least one matching relationship, and then create a Find Unmatched query that relates to that. Is there something simpler with only one query?

 

[Len Boorman]

Only got as far as reading tablerelationships create many to many.......Nope

You have 1 to Many or 1 to 1. PK to FK referential integrity enforced

len

 

[David R]

Did not copy, repeat, over.

tableRelationships holds the Primary Keys for both other tables, thus creating a many-to-many relationship. That part is not in question; anyway I think I maybe got it working, though I'll appreciate any more elegant solutions that come up.

 

[Len Boorman]

Okay misunderstood your description sorry about that.

Query between tables Groups and Relationships but edit the join property to include all from Groups and only those from relationships.

Then in criteria for selected field from relationship enter Is Null

The theory behind is that you select Groups that do not have an entry in relationships will actually be displayed as Nulls

Sorry about misunderstanding

Len