Excuse me if this seems rather elementary, but I'm just not seeing the best way to do this.

 

I've been asked to write a report for all inmates in a prison that have NOT been convicted of certain offenses.  The problem is that offense are actually statute numbers that are very complex. 

 

Pulling the data is not the problem, as it's readily available.  Basically, I grouped by the inmate number, and put everything he's ever been convicted of in his "career" in the detail line.  What I have looks like this:

 

INMATE#:  123456    INMATE_NAME:  JOSEPH D. JAILBIRD 

 

      123.12

      234.12(1)

      234.125(2)

      234.127

      301.01

      301.01(1)

      301.01(1B)(2)

 

 

So you can see, the inmate number and name are grouped as Group 1, and the statutes he is convicted of are details. 

 

Now, they have given me a list of statues that should exclude inmates from being on this report.  The problem I have is the way the statute numbers are.  For instance, assume that I DO NOT want to include anyone with a 301.01 conviction on the report. 

 

I used a formula that said:

 

"if (InStr({offender_offenses.stat},"301.01") > 0) then 1

 

The Idea being that if the formula returned a "1", I could do a group level sum on them and omit any offender where the sum was greater than 0. 

 

But, my formula will also assign a "1" to 301.01(1) and 301.01(1B)(2), since they contain "301.01" in the string.   I do not necessarily want that. 

 

Any ideas on how to get around this?

 

Thanks! 

"not equal to" 301.01...  Then it would automatically exclude those from the report.

 

Or... what about just excluding those in your SQL statment so they don't even come into the report.

 

WHERE offense NOT IN ("301.01", etc)

 

I'm not sure I'm following.   I'm not trying to suppress the statute of "301.01" or whatever, I'm trying to suppress the offender who has been convicted of it, ever.  So to do that, I think I need to bring it into the report and then suppress the inmate who has one of the statutes in his history. 

 

It's a one to many relationship -- there's only one offender number per offender, but some of these guys have been convicted of 20 or 30 things over the years.   (Yeah, some people just don't learn, it would seem.)

 

Anyway, the idea is that in order to suppress an offender who has been convicted of 301.01, I need to bring 301.01 into the report, because if I don't, I won't know who was convicted of it.   :) 

 

I also avoid 'NOT IN" queries at all costs.   This database was migrated over from an old mainframe system, which itself was brought in from an even older mainframe system...   Thus, there are millions of convictions going back to the mid 1960s, and the DBAs around here will hunt you down if you use "not in" on an un-indexed field on such a large table.   One guy did this -- he started his query on Friday afternoon, locked his workstation and went home for the weekend.  It was Sunday afternoon when some Maintenance scripts kicked off that the weekend duty DBA noticed the thread was still open...  The DBAs were not impressed.   LOL