Many to Many relationship in crystal table structure

[only412c]

I am trying to tied two table to a third table to share retrieve certain information into a report.

Table #1 has the payer id and payor name which consist of multiple payers forming a many relationship of payer id.

Table #2 has item id and item name which consist of multiple items forming a many relationship of item id.

Table #3 must associate with payer id from table #1 and item Id in table #2 to retrieve item amount and item discount.

Table #1 and table #2 are AS400 tables and the table#3 is an MS access table that includes Payor ID ITem ID, item amount and item discount field.

final outcome should be to obtain item amount and item discount based on the different payor in table #1 and and item # in table #2.

The report will look as follows

IS there a way to link Table #1 which many-to-many payor ids and table#2 which has many-to-many items to table #3 to retrive ITem amount and item discount?

Table #3 has been defined to include certain payor and items to match table #1 and table#2 field values.

Hard to explain, but let me know if you need more understanding.

Thanks

 

[IdoMillet]

Are you saying that the same Payer ID has many Payer Names (with the same ID)?

If not, then simply add both data sources to the Crystal report and then join.

If the situation is more complex, you can link the two AS400 tables into Access, and use Access as the only data source.

- Ido

Visual CUT & DataLink Viewer:
view, e-mail, export, burst, distribute, and schedule Crystal Reports.

http://www.MilletSoftware.com

 

[synapsevampire]

As Ido suggests, LINK (select New Table and link, not import) the AS400 tables (ODBC will be listed on the bottom of the File Types) into the existing MS Access database, then build an MS Access query as the datasource for the report (which will provide more flexibile and faster than Crystal).

-k