Inner and Outer Joins Alternative 2

[chrissypchy]

Hi Everyone,

I am writing a report with about 14 tables in it. Each table has an attribute "customer_id" and another attribute called "status". I would like to make a report that lists the customer_id when the status is not equal to zero for EACH TABLE. The problem I am having is that one table will have 1 with that status, another has two, and sometimes they dont have any. When it doesnt have any, NOTHING from any table shows in the results. I know this can be fixed with a join but since I do not know which tables are going to have which values, I'm not sure which ones to join where. I plan to run this report accross many of our databases so usually we wont know which tables have how many customers with 0 status. Is there any way I can make each table an individual entity so that the number of attributes dont have to match up? Kind of like separate reports all in one? Sorry for the long post. Anyones help would be greatly appreciated.
-Chrissy

 

[lbass]

Do you have a master table that contains all customer IDs? And is zero a default value for nulls, i.e., do you have "convert null field values to default" checked in report options? How do you have your tables linked now?

-LB

 

[Ngolem]

create a main report based on one table and a subreport based on another table and join them by appropriate criteria.

This is the best way to solve join problems where you would like to outter join but must specify values for selection in both tables....effectively killing the join.

Jim Broadbent

 

[chrissypchy]

LBASS,
I wish I did have a master table and that 0 was the default null value (it would make my life so much easier)but it's not. Though they are linked through many different keys, I need this particular report to ignore the links. I'll remember to check those settings though. That is a good thing to look out for. Thanks a lot for your help.

Ngolem,
The sub-report thing is working! Thank you! I only thought you could only do three sub reports, but if you keep creating the sub reports from the main one, you can make as many as you need.

I will mark both of your reports as expert. For some reason, my computer at work isnt allowing me to do it but as soon as I get home I will. Thanks again.

-Chrissy

 

[Ngolem]

glad to be of service

Jim Broadbent