In this but not that

[denniston]

I'm working on a custom report that will report all of the people who have been in a certain group of activities, but are not currently in another group of activities. What I'm wanting to accomplish is to know who has attended Sunday school in the past, but is not currently signed up for a Sunday school class.

Currently my query is:

Code: Select all

(Activity Number ranges from 7200 to 7250) and
Activity Number is not equal to 7260 and
Activity Number is not equal to 7270 and
Activity Number is not equal to 7280 and
Activity Number is not equal to 7290 

I'm not sure what this is giving me, but the result is way more than I anticipated.
I think it might just be giving me all of the people in the range.
I assume it's a simple syntax error. Is there any documentation on the expression or query syntax?

Thanks

Jason Denniston

[Zorak]

The problem is you aren't able to report on things that don't exist with custom reports, like people who haven't given or people not in an activity. The way the data is being queried, it looks at those existing activity records and ties names and addresses to them, rather than looking at all membership records, tying the activities people are or are not in.

The way your query is structured, you are selecting everyone in nearly every activity. In brackets you have activity numbers from 7200 to 7250. Then outside the brackets with an AND connector, you are listing a few activities that you don't want to be included. Everything else is.

[denniston]

Any suggestions how to accomplish it?

[Zorak]

If you are familiar with Access, you can use the Export to Access utility to do the more complex queries that aren't possible in the custom report writer.

Otherwise, I think you would have to run a report of people in the selected activities, then run a report of people not in selected activities and bring the two lists together to compare in Excel.

[denniston]

Do you know if there are any plans to improve PowerChurch's reporting functions? I have done quite a bit with mysql databases in the past, and this seems like the most rudimentary of database functions. It is extremely frustrating for us to invest so much time and effort to get our database set up and our data inputted only to find out that the program can't provide the information we need. This is the third situation for us where we have wanted data, and the advice given to us is to find another tool. Sadly, if PowerChurch can't provide us with basic information such as comparing records, we may have to do just that...find another tool.

[Zorak]

The next major release of PowerChurch will feature a completely different reporting system.

[denniston]

That is great news. Is there an anticipated release date?

[Zorak]

We're still a ways off, maybe another couple of years. It will be a complete rewrite of the program on a whole new platform.

[denniston]

Wow that's disappointing. Two years to limp along is a long time.

[NeilZ]

Wow that's disappointing. Two years to limp along is a long time.

The problem is that they're moving from a Foxpro based system to something completely new, but maintain the same functionality, look and ease-of-use. It would be child's play for a company the size of Intuit, however, Powerchurch is not that large a company, so the people working on the new version, are the same ones who have to maintain the old version.

Then imagine the kind of testing that will need to be done to insure that the new version, when released, does not introduce new bugs.

In your case, have you tried this:

All members should be in an activity called Worship (100), using that as your main source, you can then say something like:

Pseudocode of course:

Select all from Activities where
Activity Number = 100
and not in (select all from activity where activity number > 7200 and < 7400)

Considering the complexity of this code, you may need to export the data to Access tables, and create the query there. Yes, its another step, but for the time being, it may be the easiest way to do it.