Query Help

[BP]

Hi,
I have a table where I keep track of volunteer titles. It simply has three
fields, Person, Job Title and Activity Name. The Person field is tied back
to a Person table.

I am wanting to write a query that would show me any "person" who has
mutliple records in this table where Activity Name is different. For
instance, Mary Smith has been "Director" in Activity1 and "Music Helper" in
Activity2. Spit back out the person names, job titles and activity names.

Thanks in advance.

 

[John Spencer (MVP)]

Since you have spaces in your field names, you can't easily use subqueries. So
use "stacked queries".

QueryOne:
SELECT DISTINCT Person, [Job Title] as JobTitle
FROM [YourTable]

Now, use that to identify people with more than one [JobTitle]

QueryTwo:
SELECT Person
FROM QueryOne
GROUP BY Person
HAVING Count(JobTitle) > 1

Now use that get your final list

SELECT Distinct T.Person, T.[Job Title], T.Activity
FROM [YourTable] As T INNER JOIN QueryTwo as Q
On T.Person = Q.Person

You can also combine querytwo with the last query

SELECT Distinct T.Person, T.[Job Title], T.Activity
FROM [YourTable] As T INNER JOIN QueryTwo as Q
WHERE T.Person in
(SELECT Q.Person
FROM QueryOne As Q
GROUP BY Q.Person
HAVING Count(Q.JobTitle) > 1)