syntax for WHERE NOT IN?? to locate orphaned records

[pgcn]

Hi

I have an A2003 database of contact activites which are allocated
participants (stakeholders), however when a contact is deleted the
code only seems to be deleting the contact from the Contact table and
not the stakeholders as well (trelContactStHolderProj). I'd like to
know the query to show orphaned records in trelContactStHolderProj
where there's no matching ContactID in tblContact.

tblContact:
ContactID - PK - Auto number, joined 1 to many with

trelContactStHolderProj:
fContactID - PK - number

so far I have:
SELECT tblContact.ContactID, trelContactStHolderProj.fStHolProjID,
tblContact.fContactType
FROM tblContact INNER JOIN trelContactStHolderProj ON
tblContact.ContactID = trelContactStHolderProj.fContactID;

thanks a lot

Peta

 

[Allen Browne]

Peta, when you go to create a new query, there's an Unmatched Query Wizard
that will do what you want.

Once you have that sorted out, you might want to use a cascading delete on
the relationship. In the Relationship window, double-click the line joining
the 2 tables. In the dialog, make sure the box for Referential Integrity is
checked, and also check the box for Cascading Delete.

 

[MGFoster]

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

Use a LEFT JOIN for this type of query:

SELECT P.ContactID, P.fStHolProjID
FROM trelContactStHolderProj As P LEFT JOIN tblContact As C
ON P.fContactID = C.ContactID
WHERE C.ContactID IS NULL

This means you want the info in the "LEFT" table that doesn't have
matching data in the "RIGHT" table (tblContact).

You can't show data in the SELECT clause if the row (Record) isn't in
the referenced table (in your case tblContact): orphan means there
isn't any data in the parent table; logically, that wud be tblContact.
--
MGFoster:::mgf00 <at> earthlink <decimal-point> net
Oakland, CA (USA)
** Respond only to this newsgroup. I DO NOT respond to emails **

-----BEGIN PGP SIGNATURE-----
Version: PGP for Personal Privacy 5.0
Charset: noconv

iQA/AwUBR4nXyYechKqOuFEgEQJJfACfR7C5W0yR9w4Q7SvhFV1dQBI4my0AnRCe
oKGPEk0ddJctWjgBgJ8IdNeJ
=y7Yq
-----END PGP SIGNATURE-----

 

[pgcn]

Peta, when you go to create a new query, there's an Unmatched Query Wizard
that will do what you want.

Once you have that sorted out, you might want to use a cascading delete on
the relationship. In the Relationship window, double-click the line joining
the 2 tables. In the dialog, make sure the box for Referential Integrity is
checked, and also check the box for Cascading Delete.

--
Allen Browne - Microsoft MVP.  Perth, Western Australia
Tips for Access users -http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.













- Show quoted text -

Cheers again Allen - so simple when your shown the way! Now I just
have to clean up the tables.

Regards
Peta