Searching for a NULL value 2

[WestView]

Hello All,

This is similar to a previous posting, but different enough that I can’t figure out how to make it work from an excellent reply to that posting from MrBill.

Here goes.

I have a simple 3-table report: MEMBER, SUBSCRIPTION and DETAIL.

The MEMBER table holds member info, the SUBSCRIPTION table holds the subscription info for the member and the DETAIL table holds info on the member’s subscription level.

The MEMBER and SUBSCRIPTION tables have a typical one-to-many relationship. The MEMBER and DETAIL tables have a one-to-one relationship

The MEMBER table has a field called ‘Type’ that indicates the member’s category. Members are either Individuals (IN), Companies (CO) or Branches (BR).

The MEMBER table also has a field called ‘Company_ID’. All records – with the exception of (CO) records – should have this field populated with the ID of the related (CO) records. The hierarchy being: CO -> BR -> IN.

A company’s branch (BR) may purchase a subscription to one of our products. Individuals (IN) who work for that branch may be granted access to an (on-line) subscription. If they are, it is indicated in the DETAIL table where the branch (BR) ID is entered --- this is the MEMBER.ID for a BR member category.

For example, ACME Company has a branch, “ACME New York” (ID #1234), that purchased one of our on-line subscription products. “ACME New York” then granted access to this product to 5 of it’s employees. Therefore if Jane Doe (ID #5678) is an employee of “ACME New York” and has been granted access to this product, she would then have #1234 populated in the DETAIL.Branch_ID field of her matching record giving her access to this product. ONLY branch (BR) records can do this!

I need to create a report that returns those branch (BR) records where they have purchased one of our on-line products but have NOT granted access to any of their employees. That is, like in the example above, Jane Doe (ID #5678) would NOT have #1234 populated in the DETAIL.Branch_ID field of her matching record.

How to do this?!?!?! Is it possible to search for NULL values? Am I making this more complicated than I need to?

Any/All help in this would be greatly appreciated!!!

Thanks,

Tom

 

[lbass]

There should be a left join from Member to Detail. Then you should be able to use a record selection formula of:

isnull({Detail.Branch_ID})

-LB

 

[WestView]

LB,

Thanks for you reply! However, because I'm such a dolt, I was hoping for a little more help/direction. Specifically, how I would go about setting up the tables. I’m assuming I would need to alias some tables to got all the (CO) records vs. the (BR) and (IN) records etc.

Thanks again!

Tom

 

[lbass]

I would just do a left join FROM the Member table to the Details table, and and equal join from the Member table to the Subscription table, since you only want to include records where there is a subscription. If this doesn't do the trick, then I think it would be helpful if you provided some sample records.

To understand how your data works, try these joins before adding the selection formula (isnull({Detail.Branch_ID})), and then place the relevant fields in the detail section. Then you can note what records you want to see in your final report. Add the record selection criteria and verify that that's what you get.

-LB

 

[WestView]

LB,

Thank you for your reply. Sorry for not getting back to you sooner. Where still digging out here in the Northeast.

Anyway, what I’m trying to do is get the report to do this: Display all the BR records where the BR has purchased a subscription but not granted access to any employees of that branch.

For example, using the records below, both Acme NY & Acme LA have purchased subscriptions. However, only Acme NY has granted any of its employee’s access (Jane Doe #5678) by populating its Branch_ID in Jane Doe’s DETAIL record. Therefore, I would like to see Acme LA displayed on this report but not Acme NY.

MEMBER TABLE:

ID Type Name Company
5679 BR Acme NY Acme
5678 IN Jane Doe Acme NY
5677 IN Bill Smith Acme NY
5676 BR Acme LA Acme
5675 IN John Jones Acme LA

DETAIL TABLE:

ID Branch_ID
5678 5679
5677 NULL
5675 NULL

I hope this clarifies the problem I’m having with this a little more.

Thank you again for your post!!!

- Tom

 

[lbass]

Did you try my suggestion? Please provide feedback after trying.

-LB

 

[WestView]

LB,

Yes, I'm sorry, I did try your suggestion. However, it pulls only those records where a BR or IN record has a subscription. In my scenario, an IN record may NOT have a subscription, but be granted access to the BR’s subscription through the DETAIL table.

Thanks again!

- Tom

 

[lbass]

Then change the join from member to subscription to a left join.

-LB

 

[WestView]

LB,

Thank you for your last reply and your patience. I’m sorry, but I just can’t seem to get my head around this thing.

I problem, as I see it, is not the join between the MEMBER and DETAIL tables but that this requires some sort of table alias or perhaps a SQL Expression because: Records in the MEMBERS table identified as IN (individuals) may NOT have a subscription to any of our products.

IN (individuals) may get access to an on-line subscription to one of our products via the Branch_ID field being populated in the DETAIL table - which indicates what Branch (BR) record ID has granted them access.

Therefore, I believe I need to pull all those Branch (BR) records that have an active subscription to one of our products, then pull all Individual (IN) records who are employees of that Branch - which have been granted access to that subscription via the Branch_ID field being populated on their record in the DETAIL table.

This is the bit I just can’t figure out how to do.

The Company ID (CO_ID) is one value that links both the IN and BR records.

MEMBER TABLE:

ID Type Name Company CO_ID
5000 CO Acme
5679 BR Acme NY Acme 5000
5678 IN Jane Doe Acme NY 5000
5677 IN Bill Smith Acme NY 5000
5676 BR Acme LA Acme 5000
5675 IN John Jones Acme LA 5000

DETAIL TABLE:

ID Branch_ID
5678 5679
5677 NULL
5675 NULL

Thank you again for all you help and patience so far!!!

- Tom

 

[lbass]

Let's back up a little. Please explain your links for all three tables, identifying which fields you are linking and what kind of join you are using. It appears that it is branches not the company that can have the subscriptions, but I am confused about who you are trying to identify. Initially you said,

I need to create a report that returns those branch (BR) records where they have purchased one of our on-line products but have NOT granted access to any of their employees.

But then in your penultimate post you said:

However, it pulls only those records where a BR or IN record has a subscription. In my scenario, an IN record may NOT have a subscription, but be granted access to the BR’s subscription through the DETAIL table.

I thought you wanted those individuals without access to a BR's subscriptions. Your last comment makes it sound as though somehow individuals can independently have subscriptions separately from the branch. This totally loses me.

-LB

 

[WestView]

LB,

Yes, sorry for the confusion. My fault! You’re right. I DO want a report that returns those branch (BR) records where they have purchased one of our on-line products but have NOT granted access to any of their employees yet.

Let me clarify, individuals (IN) – who are not employees of a company/branch – may purchase subscriptions to our products. However, the product(s) I want in this report are ones that are purchased by company branches, who then grant access to this subscription their employees (IN) via the Branch_ID field being populated on their matching record in the DETAIL table.

I hope this clears up my mistake.

And again, thanks for you help and for hanging in there with me on this one. I very much appreciate it!

- Tom

 

[lbass]

You didn't respond to:

"Please explain your links for all three tables, identifying which fields you are linking and what kind of join you are using."

This is relevant (I believe).

-LB

 

[WestView]

LB,

Yes, sorry. They are joined as follows using equijoins to each.

MEMBERS.ID <--> DETAILS.ID
MEMBERS.ID <--> SUBSCRIPTIONS.ID

Thanks again!

- Tom

 

[kskid]

As LB suggested, change

MEMBERS.ID <-Left Outer Join-> SUBSCRIPTIONS.ID

Cheers,
-LW

 

[lbass]

Can you also please provide the fields available in the subscription table with some sample data?

-LB

 

[lbass]

Okay, try adding an alias table Member_1 and then try this for joins:

{Member.ID} ->equal->{Subscriptions.ID}
{Member.CoID}->equal->{Member_1.CoID} (alias)
{Member_1.ID}->leftjoin->{Details.ID}

Your record selection formula should be:

isnull({Details.ID}) and
{Member.Type} = "BR" and
{Member_1.Type} = "IN"

-LB

 

[WestView]

Hi LB,

Thank you for your reply!!!

I think this is the kind of thing I'm looking for. However, when I made the the table alias and joins you suggested and put the Record Selection formula in, I get no data.

I tried changing

isnull({Details.ID}) and
{Member.Type} = "BR" and
{Member_1.Type} = "IN"

to: isnull({Details.Branch_ID}) and
{Member.Type} = "BR" and
{Member_1.Type} = "IN"

- but get only partial success. This pulls all records where ANY of the Details.Branch_ID records is null. I need to pull only those records where all the qualifying records in the DETAIL.Branch_ID field are null.

In response to your previous question, the SUBSCRIPTION table is basically this:

SUBSCRIPTION TABLE

ID Product_Code Status Paid_Thru_Date
5679 Subscription_01 A 1/1/2006
5678 Subscription_01 I 1/31/04

Again, THANK YOU for all your time & patience!!!

Tom

 

[lbass]

Okay, I think I see. Remove the isnull() clause from the selection formula. Then create a formula {@someaccess}:

if not isnull({Details.Branch_ID}) then 1 else 0

Assuming you have a group on {Member.ID}, then go to report->edit selection formula->GROUP and enter:

sum({@someaccess},{Member.ID}) = 0

This should return only those branches where no individuals have access.

-LB

 

[WestView]

LB,

BINGO! That was it!!!

I can't possibly thank you enough for hanging in there with me through this! I don't know why, but I just couldn't get my head around this problem. I'm still not quite sure I fully understand it, but you can be sure I will be going over your solution until I do.

Again, THANK YOU so much for your invaluable help!!! You're the best!

- Tom