Direction of links when using multiple tables

[Guest_imported]

Hi,

I have a many to many relationship between Microsoft Access tables. I need to understand the direction of the links in Crystal.

The Help screen in Crystal Reports states that the direction of the link shoulf flow "from" the primary table "to" the foreign table.

I book I purchased states the opposite. The link should flow "from" the foreign table "to" the primary table.

With only two tables, it does not seem to matter. However, in a many-to-many relationship there can be many tables. When I link according to Crystal, one table always gets left out.

When I link according to the book, everything works fine. Is crystal wrong, or is there something that I am missing?

Any help is appreciated.

 

[kenhamady]

It depends on your environment.

If you are in a SQL environment, the arrow direction doesn't make any difference to an equal join. If you use an outer joins the direction of the arrow is Left to Right which determines which table is included. Left outer joins are usually better supported in databases.

If you are in a non-odbc environment all joins behave as left outer joins with the arrow going left to right. The arrow has to link to an indexed field that matches the other end of the arrow.

You should avoid having two arrows pointing "into" the same table. Ken Hamady, On-site Custom Crystal Reports Training & Consulting
Public classes and individual training.
Guide to using Crystal in VB

http://www.kenhamady.com/

tek@kenhamady.com

 

[Guest_imported]

With Respect, this doesn't tell me anything. Left to right? Which is left, which is right. Primary or foreign, Parent or child?

I am using Microsoft Access tables. I have a many-to-many relationship, which is two one-to-many relationships combined.

Again, in this exact scenario, Crystal says the direction is always from the "Primary" table into the "Foreign" table. A reference manual that I have says FROM the "Foreign" table TO the "Primary" table.

Which is correct, in a PC style environment?

Thanks to anyone who could help

 

[kenhamady]

The sources are conflicting because they are both describing a preference, not an absolute. The arrow can go either way as long as you have an index to link to. Primary/Foreign or Parent/Child have nothing to do with the direction of the arrow. That doesn't mean that the direction is irrelevant in your environment. The direction of the arrow determines which table can provide records that do not have a match in the joined table.

I assume that "PC Style" means non-ODBC. All joins that aren't ODBC behave as Left Outer joins, which means that records from the the table on the "Left" side of the join (the Dull end of the arrow) can still show up in the report, even if there are no matches for them in the table on the right side of the join (pointed end of the arrow). If all of your records have matches in both tables, the direction should be irrelevant. Ken Hamady, On-site Custom Crystal Reports Training & Consulting
Public classes and individual training.
Guide to using Crystal in VB

http://www.kenhamady.com/

tek@kenhamady.com

 

[Guest_imported]

Hi,

I greatly appreciate your input, but I am still lacking a "rule." Crystal's help screen and other reference materials that I have do not indicate a preference. They are implying a specific method.

In my many-to-many relationship, if I follow Crystal's method (primary into foreign), I end up with two arrows flowing "to" the same foreign table. I get faulty results: one table is left out.

If I reverse it and link "from" foreign "to" primary I get proper results. For that matter, if one side links from primary to foreign and the other from foreign to primary I also get good results. This is way to inconsistent.

There has to be a definitive rule that governs this. Ex. All arrows must flow in the same direction, it must be foreign to primary, etc.

Does anyone "know" THE rule?

 

[kenhamady]

There are several rules, but I don't think there is a rule regarding foreign keys. I have gone both ways and get the same results. The only difference is unmatched records.

Which records in your table might not have matches in the others 2? Is the middle table always matched in the other 2? Have you tried starting in the middle and having both arrows go OUTWARD from that middle table? Ken Hamady, On-site Custom Crystal Reports Training & Consulting
Public classes and individual training.
Guide to using Crystal in VB

http://www.kenhamady.com/

tek@kenhamady.com

 

[Guest_imported]

Yes, the middle table is the foreing table. If I link "from" the foreign table "to" the primary tables it works fine. Its only when I do it the way that Crystal says it must be done do I get wrong results.

I have the following tables:
Orders(Primary) Orders Detail(Foreign) Inventory(Primary)

If I link primary to foreign, the Inventory table is always ignored. If I link foreign to primary there is no problem.

If I am linking only two out of the three (one primary and the foreign) the direction of the arrow does not seem to matter.

The problem is not getting the results that I want. It is simply understanding a defined rule on the subject. About all that I can state at this point is that directing two arrows into the same foreign table seems to cause a problem. I suppose that is a start, but there is a piece of the puzzle missing.

Thanks

 

[kenhamady]

Having worked with CR for many years and versions, I think you give their documentation too much credit. In a NON-ODBC environment, pointing 2 arrows INTO a single table is telling CR to start at two different places at the same time. In V8 that will actually give you a warning message.

What version are you using?
Where are you getting these linking instructions? Ken Hamady, On-site Custom Crystal Reports Training & Consulting
Public classes and individual training.
Guide to using Crystal in VB

http://www.kenhamady.com/

tek@kenhamady.com