Linking 2 separate detail tables to one master table

[triffe]

I am having problems linking three of my tables on a Crystal Report I created. (I have more than 3 tables on my report, but all other tables seem to be linked without any problems.) I have a master table GL00100 which holds account numbers and descriptions. I have two detail tables PM10100 and PM30600. PM10100 and PM30600 have no relationship between them. PM10100 is an open transaction table. PM30600 is a historical transaction table. Transactions move from PM10100 to PM30600 after they are posted. What I am trying to do is link PM10100 to GL00100 and also link PM30600 to GL00100. Both the PM10100 and PM30600 have a DSTINDX field that holds the index value that is unique to the ACTINDX field in the GL00100 table.

I have done a left outer join link from PM10100 on the DSTINDX field to the ACTINDX field in the GL00100 table. I have also done a left outer join link from the DSTINDX field in the PM30600 table to the ACTINDX field in the GL00100 table.

When I run my report, I am pulling the account number and description from the GL00100 table. For some reason, the account number and description appear for the records that correspond to the PM30600 table, but the account number and description are missing on the records that correspond to the PM10100. Just for testing purposes, I removed the link from the PM30600 table to the GL00100 table, and then the account number and description showed for my PM10100 records.

I'm not sure how to fix my linking issue to get the account number and description for transactions from both the PM10100 and PM30600 tables. Any help would be greatly appreciated!

 

[lbass]

You can't link from two tables to the same table. If GL00100 is the master table and has all accounts, then do a left join from GL00100 to PM10100, and a left join from Gl00100 to PM30600. This allows for the possibility that each of the detail tables might not have a record that matches a record in GL00100, but allows you to display the record from the master table anyway.

-LB

 

[kenhamady]

The problem that you are going to have is that you have 2 different one-to-many relationships, so you are going to get a cartesian result from the two relationships. You are either going to have to do a UNION query to append the history on to the current, or you are going to have to use subreports.

Ken Hamady, On-site Custom Crystal Reports Training & Consulting
Public classes and individual training.
Expert's Guide to Formulas / Tips and Tricks / Guide to Crystal in VB

http://www.kenhamady.com/

- tek@kenhamady.com