Using "and" in a query

[Guest]

How do you use "and" in a query to find where something appears twice?
For example I have a table of Staff Names, a table of Departments, and a
table that connects them to each other. One staff member may "belong" to more
than one department - how do I create a query find staff members belonging to
more than one department?

 

[Brian]

How do you use "and" in a query to find where something appears twice?
For example I have a table of Staff Names, a table of Departments, and a
table that connects them to each other. One staff member may "belong" to more
than one department - how do I create a query find staff members belonging to
more than one department?

Let's assume that your "linking" table is called staff_in_departments, and
it contains the fields staff_id and department_id. Your query will then be
as follows:

SELECT staff_id FROM staff_in_departments GROUP BY staff_id HAVING
Count(department_id)>1

 

[Jeff Boyce]

Betsy

If I understand your data model, one staff member can "belong to" more than
one department, and one department can "belong with" more than one staff
member. This requires three tables (Staff, Department, StaffInDepartment.
If true...

A query of the "middle" table (StaffInDepartment - i.e., resolver, junction,
etc.), where DepartmentID = (whatever) will reveal all StaffMemberIDs
associated with that Department. No "And" required.

If you'd like to see the actual StaffMember info, join the StaffInDepartment
table to the Staff table, but still select for a department. Add in
whatever fields you want to see.

.... or have I misunderstood...?