problem designing a query

A

Amit

Hi,

I'll appreciate any help with designing a query. I have
the following tables:

1. Person Table (Person ID-PK, Person Name, ...)
2. Staff Table (Staff ID-PK, Person ID-FK, Staff Status)
3. Partner Table (Partner ID-PK, Person ID-FK, Partner
Status)
4. Representative Table (Rep ID-PK, Person ID-FK, Rep
Status)

I want to get the names of all the people whose status
is "Active". I've tried using a query wizard, and design
view, but am unable to get this query working. Thanks for
any help.

-Amit
 
P

paul

open new query in design view - select staff table and
person table and link the two by person id-pk, then
d/click the fields you wish to see in query + include the
staff status field - then in the criteria line of query
enter "active" and select run to check out your results
 
L

Lynn Trapp

You probably need a UNION query:

SELECT Person.PersonName, Staff.Status
FROM Person INNER JOIN Staff ON Person.PersonID = Staff.PersonID
WHERE (((Staff.Status)="Active"))
UNION
SELECT Partner.PartnerName, Partner.Status
FROM Person INNER JOIN Partner ON Persons.PersonID = Partner.PersonID
WHERE (((Partner.Status)="Active"))
UNION
SELECT Representative.RepresentitiveName, Staff.Status
FROM Person INNER JOIN Representative ON Persons.PersonID =
Representative.PersonID
WHERE (((Representative.Status)="Active"));
 
Top