filtering by Skill Number

[billyg73]

Hi there,

I'm trying to create a custom report based on Skill Numbers that people may or may not have, and need your help/advice.

In our database, we assigned Skill Number 1 as Plan to Protect, a training for those dealing with minors. Skill Number 1100 is for those involved in Youth Ministry.

What I want to do is create a report where I list all members currently involved in Youth Ministry, but who have NOT yet finished the Plan to Protect training.

i.e. Skill Number equal to 1100 and Skill Number not equal to 1

However, I cannot get the Custom Report to correctly select these people. Any thoughts?

Blessings,

Graham Faulkner
gfaulkner@watpen.org

[NeilZ]

Looks like you're looking for something like this, which I'm not sure you can do in custom reports:

Select * where skill = 1100
and not in (select * where skill = 1)

[Zaphod]

I don't think there's a good way to handle that situation in Custom Reports without getting into something really complicated (if at all). It certainly won't do the subselect NeilZ has suggested. You may be able to use something like Access to connect to the database files via ODBC (foxpro odbc driver available on microsoft.com), and use the report writer there to do something like NeilZ suggests.

Note that we don't support this type of thing, so you're on your own. We would HIGHLY encourage you to use the database READ-ONLY for hopefully obvious reasons.

[tgknowles]

Hi,
You can use Custom Reports to obtain those persons with Skill Numbers of "1100" and not "1" by using the print switch in the properties for each field in the body of a column report.

For example:
Set filter to obtain those persons with Skill Numbers of "1100" or Skill Numbers "1"

Include these fields in your report:
Last Name, First Name
Skill Number
Other fields you need

Group on "Last Name, First Name"
SUM on "Skill Number"

Your data will have "1101" for persons with both Skill Numbers and "1100" for those persons with only "1100" skill number.

Edit the column report as you like, only be sure to set the properties for each field in the body to Print "If Skill Number = 1100"

The result should be the persons you are looking for. If two people have the same name you will have to deal with that.

I've done this on some of my reports. I just hope that my memory is close enough for you to get the idea.

Good Luck!
Thomas