How do I find organizations with no members?

  • Thread starter Thread starter Warren
  • Start date Start date
W

Warren

I have two tables in a 1 to many rel. How do I find organizations with no
members? Thanks. Here's my two tables...

ORGANIZATION
OrgID (pk)
OrgName

MEMBERS
MemID (pk)
MemberName
OrgID (fk)
 
Create a query that joins the tables on the OrgId field.
In the query desinger, right click on the join line and select the the
options the says show all records from the organization table and only
matching records from the members table.

Include the OrgName and MemberName fields

Now make the query a totals query (click on the tool bar icon that looks
like the Greek letter Sigma).

Select Group by for both fields

Set the criteria for the MemberName field to Is Null

Your query will then present a list of Orgs with no members.
 
I have two tables in a 1 to many rel. How do I find organizations with no
members? Thanks. Here's my two tables...

ORGANIZATION
OrgID (pk)
OrgName

MEMBERS
MemID (pk)
MemberName
OrgID (fk)
Click to expand...

Use the Unmatched Query Wizard in query design, or - pretty much equivalently
- copy and paste this SQL into the SQL window of a new query:

SELECT OrgID, OrgName
FROM Organization LEFT JOIN Members
ON Members.OrgID = Organization.OrgID
WHERE Members.OrgID IS NULL;

This "frustrated outer join" will retrieve all organizations (because of the
left join); if there are matching records in Members it will retrieve the
OrgID from the Members table. If there is no matching record it will show
Members.OrgID as NULL - and the criterion IS NULL will select just those
records.

John W. Vinson [MVP]
 
You have to create the join.
Position your cursor on the OrgId field of the Org table and drag and drop
to the OrgId field of the Member table. You will get a black line between
the two.
 
Back
Top