Linking three Tables

[cgtrucking]

I have three tables that I am linking.
CI02(Container History File),CM03(Customer Master File) and CM05(Service Master file).

I was able to get most of the info from the first two tables, but once I added the third table(CM05) I got thousands of entries. Most were duplicates of previous entries.

I linked the CI02 to CM03 by the Customer Number and everything was working great. Then I added the 3rd table (CM05)and linked it to CM03 by the Service number.

I am using Crystal Reports 10.0

Any ideas on what I am doing wrong.
Thanks
Fran

 

[kskid]

I am guessing that there is a one to many relationship between the Customer Master file and Service Master file. You gave table names but not what it contains so I am assuming that you can have more than one se3rvice master record for each customer master. If that is the case, you must then decide which service master record you want.

-lw

 

[cgtrucking]

The only record that I need from the service master record is th Service Type.


The customer # is common to all three tables. The service number (CM05_SERV) and (CI02_SERV)are common to CM05 and CI02. I have tried various combinations to link the three files together with the same results. Any other ways around to eliminate the repetitive records. I am still getting hundreds of thousands of records. I should only be getting approx 1240.

Currently, I have the tables linked as follows

CM05_SITE to CI02_SITE
CI02_CUST to CM03_CUST

Any recommendations
Thanks
Fran

 

[lbass]

I think you should show some sample detail level data so we can see how the data looks.

-LB

 

[cgtrucking]

Is there a way to attach files? I tried earlier but had not luck.
Fran

 

[kskid]

Knowing a little bit about your data structure will go a long ways to providing you with the right solution.

As I see it, your premis about customer_# in all three tables and your table linking as shown brought up a lot more questions.

CM05_SITE to CI02_SITE
CI02_CUST to CM03_CUST

Bottom line.... given the three tables, what is the purpose of your report? Are you wanting summary information by customer, container or service type or combination of thereof.

Based on your links, it appears that you can have more than one site per customer. Given that, is there a unique customer_# per customer/site combination or do you use the same customer_# regardless of site.

-lw