Find unused linked field

[greeknl via AccessMonster.com]

Is it possible to find a value in a table which is linked to another and is
not used. And if so, how to go about it.

Example:
I have a table with all the data for companies and a table with contact names
and addresses which are linked. What I would like to do is find the names of
the companies (or companyID) which are not linked to any persons in the
contacts table.

Table Companies
CompanyID
Companyname
....

Table Contacts
ContactID
ContactName
CompanyID
....

 

[pietlinden]

Is it possible to find a value in a table which is linked to another and is
not used. And if so, how to go about it.

Example:
I have a table with all the data for companies and a table with contact names
and addresses which are linked. What I would like to do is find the names of
the companies (or companyID) which are not linked to any persons in the
contacts table.

Table Companies
CompanyID
Companyname
...

Table Contacts
ContactID
ContactName
CompanyID
...

use the find unmatched query wizard or use an outer join.
Create the query as normal. Drop both the Contacts and Companies
tables into the QBE grid. then create the link between the CompanyID
fields in both tables. then Right-click on the join line. select the
option that shows all values from the Companies table and the matched
values from the Contacts table. then set the criteria on the
ContactID field to IS NULL.

It should look something like this:

SELECT *
FROM Companies LEFT JOIN Contacts ON
Companies.CompanyID=Contacts.CompanyID
WHERE Contacts.ContactID IS NULL;

Hope this helps.

 

[greeknl via AccessMonster.com]

This worked great, Thanks

Groeten uit Griekenland

Is it possible to find a value in a table which is linked to another and is
not used. And if so, how to go about it.

[quoted text clipped - 18 lines]
use the find unmatched query wizard or use an outer join.
Create the query as normal. Drop both the Contacts and Companies
tables into the QBE grid. then create the link between the CompanyID
fields in both tables. then Right-click on the join line. select the
option that shows all values from the Companies table and the matched
values from the Contacts table. then set the criteria on the
ContactID field to IS NULL.

It should look something like this:

SELECT *
FROM Companies LEFT JOIN Contacts ON
Companies.CompanyID=Contacts.CompanyID
WHERE Contacts.ContactID IS NULL;

Hope this helps.