Help with query

  • Thread starter Thread starter BP
  • Start date Start date
B

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.
 
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.
Click to expand...

A Totals query based on another query is one way to do this. ANSI SQL
has a "COUNT DISTINCT" operator which is just what's needed, but
unfortunately Access doesn't support it!

First, create a Query grouping by Person and Activity Name:

SELECT Person, [Activity Name] FROM Titles GROUP BY Person, [Activity
Name];

Save this as PersonAct. Then create a second query linking Person to
PersonAct by Person, and count records there:

SELECT Person.<whatever you want to see>
FROM Person INNER JOIN PersonAct
ON Person.Person = PersonAct.Person
HAVING Count(*) > 1;

John W. Vinson[MVP]
(no longer chatting for now)
 
Back
Top