Two fields in my report:

Acct#    and    district.

 

Each acct# can have multiple districts.  So essentially one could look like this:

 

Acct#             district

12345            downtown

12345 (same)uptown

12345 (same)midtown

 

So...when I try to do a select query and choose any acct# that do not have a district of downtown....I still get ones like this one that are listed in uptown or midtown.  Because Crystal thinks, 'Okay...I won't give you the one that says downtown for that acct # but I'll give you the ones that DON'T say downtown because they fit your simple logic."

 

I understand why Crystal does this...I just don't understand how to get the results I want.  Basically...I want any and all acct#'s that look do NOT have downtown listed for them.

 

For example, I would want one that looks like this:

 

Acct#           district

4567            uptown

4567            midtown

 

This one doesn't have downtown as a district.  I want only these to show up in my report.

 

I'm not sure if this is something I do in Grouping or if it's something I do with an if else statement.  I'm SQL stupid too...so be gentle if that's my recourse.

 

And thanks in advance for any help!

Select *
from accounts
  inner join district on accounts.account_number = district.account_number
where not exists(
  Select 1
  from district as d2
  where d2.account_number = accounts.account_number
    and d2.district = {?District}

Create a Parameter called District for the command (do this in the Command window, NOT in the main parameters in Crystal!)  Or, if you'll always be running this report to exclude just Downtown, you can just put the string 'Downtown' in your command instead of creating the parameter.

 

-Dell