Sorting people in multiple activities

Moderators: Moderators, Tech Support

Post Reply
Posts: 10
Joined: Thu Feb 05, 2009 11:13 am
Location: Meridian, Idaho

Sorting people in multiple activities

Post by aatarsus »

We have Activities and Skills codes for both Sunday School Classes and Small Groups. I'm trying to produce a report of just those who are members of both a Sunday School Class and a Small Group. I can run a custom report and get all those in Sunday School and run another report and get all those in Small Groups, but when I run the report using the same queries with "and" as the connector PC tells me that there are no records matching the description. What am I doing wrong?

Posts: 9321
Joined: Wed Oct 08, 2003 12:20 am
Location: Connellsville, PA

Re: Sorting people in multiple activities

Post by NeilZ »

I'm trying to remember if this type of query can be constructed using the current report writer. I'm fairly sure it can't be done since it requires an inner join which basically returns only the records that satisfy the two queries. (IE: is in Activity 1 and and in Activity 2).

Here's a definition of an inner join:
Neil Zampella

Using PC+ since 1999.

Tech Support
Tech Support
Posts: 2652
Joined: Thu May 13, 2004 8:59 am
Location: PowerChurch Software

Re: Sorting people in multiple activities

Post by Zorak »

This is correct. You could query for activity=1 AND activity=2, but it would return zero results, since no single record in the activities database meets both conditions. If you use the OR connector, you will get everyone that is a member of one or the other or both.

If you do it that way, you could do the following:

In the output fields, make sure that "last, first" and "skill number" are included.
- Group first on "last, first"
- Count the skill number field

In the report form, enter "cnt_skill_no>=2" in the Print When for each of the output fields.

It's a bit of a hack, but it will do what you want!

Post Reply