exception query

  • Thread starter Thread starter menelik
  • Start date Start date
M

menelik

I have two tables, Project and Contact, with a one-to-many
relationship. The columns that link these tables are A and X,Y,Z
respectively. A, X, Y, and Z are of type Text. I want a query to return
all Project records that do not have a Contact record associated with
them. Here is what I thought would work:

select distinct Project.* from Project, Contact
where Project.A not like Contact.X and Project.A not like Contact.Y and
Project.A not like Contact.Z

This seems to return everything and I'm beginning to understand why. I
am at a loss of how to achieve my goal though. Any
thoughts/suggestions?

(Background info: There is currently a query that returns projects and
their associated contacts. However, projects that do not have contacts
are omitted and thus my need for this sort of "exception list".
 
Access offers a wizard that builds an unmatched query. Give that a shot.

The general approach is to look for records in Table1 where the primary key
does not exist in Table2. Does your Table1 have a primary key designated?
Does your Table2 have a foreign key, pointing back to Table1?

Try the wizard ... I believe the construction you are looking for is the "Is
Null" comparison.

By the way, your SQL statement is comparing field A in Table1 to fields X,
Y, Z in Table2 -- wouldn't you want to compare A to A, X to X, ...?

--
Regards

Jeff Boyce
Microsoft Office/Access MVP


Microsoft IT Academy Program Mentor
http://microsoftitacademy.com/
 
Thank you Jeff. I used the unmatched query wizard and edited the
statement for my specific purposes. Worked like a charm!
 
Back
Top