You can manually set your cardinality on the join when then auto-detect is unable to determine it based on table content. Just select the radio button for “N,1” or “1,N” under each table to set cardinality for the join.
In fact I never actually use the “Detect Cardinality” button as I usually know the relationship of the tables before building the join. Using the “Detect” button does require that you have a representative sample of data in the tables being joined for the “Detect” process to work correctly.
In a message dated 00-03-17 15:05:15 EST, you write:
That is not my problem… of course I know how to use the radio button
manually.
My problem is that When I press detect after selecting 1=n or n=1 or n=n ,
I
get a “unknown cardinality” message.
If you are setting the cardinality manually, then do not press the Detect button. The two processes are mutually exclusive. If the detect button doesn’t work before you set the cardinality, then it won’t work afterwards either.
Just set the cardinality that you know it to be and leave it at that.
Incidentally, the cardinality is not even required unless you are trying to resolve loops or other multiple SQL paths.
We ran into a similar problem just last week and it was because it was a concatinated join and each one was joined as a separate join and that is the incorrect way to do it. Could that be your problem? I can’t tell from your message if they are individual joins or not.
I have two tables that I will need to get a variety of selections from:
Table 1 Table 2
Cycle number Current Cycle Number
Cycle Date Current Month begin Cycle number
Previous Month begin Cycle number
Previous Month End Cycle number
In my reports, I will need to select Cycle date from Table 1 in a variety of ways. For 1 report, it may be where Cycle number(Table1)
= Current Cycle number (Table2), or it may be where Cycle number(Table1) is between Prev Mon Begin Cycle number (table2) and Prev Mon Ending Cycle number(table2), or even where Cycle number(table1) = Currtne Month begin Cycle numnber(Table2). Question is, how do I set these tables up in my universe and join them? I cant join Cycle number from table1 to all the fields in table two, or I’ll get conflicting where statements in my queries. Any suggestions?
Join the two tables in designer with a bogus join which is always true. e.g.
(Assuming Current Cycle always >= Any interesting Cycles in table 1)
table1.cycle_number <= table2.current_cycle_number (Assuming all cycles are positive)
sign(table1.cycle_number)=sign(table2.current_cycle_Number)
Then make all the other possible join conditions as pre-defined filters. Only drawback is that if a user creates a report and forgets the pre-defined condition then the results will be dubious to say the least.