Linking of Tables

[wanzek1]

I am working on a report that has 3 tables.

JCJM
JCCP
udContractMthly

I have no problems getting the information I need when I use the 2 tables JCJM and udContractMthly. Everything works beautifully. I can get my information as needed. However, I need some information from the JCCP table. No matter what changes I make to the linking that minute I add the table to my report and attempt to use a field it messes with all my other information that I had already included.

I am linking the udCOntractMthly table to the JCJM table using the Company and the Contract. When I try adding the JCCP table I try linking it to the JCJM table using Company and Job. However, no matter what changed I make and what I try it still goofs everything up.

I am using Crystal Reports 11. I really could use help. I need to get this done ASAP!

 

[hilfy]

What do you mean by "goofs up"? Is too much data showing up? Not enough? What's happening that you don't expect?

-Dell

DecisionFirst Technologies - Six-time SAP BusinessObjects Solution Partner of the Year

http://www.decisionfirst.com

 

[wanzek1]

For Example:

The # should be $12,878,283.25 but when I add the other tables it changes to $23,271,057,832.75.

Another field same thing: Should be $13,609,317.06 changes to $24,592,035,927.42.

Another field same thing: Should be $6,894,806.54 changes to $12,458,915,417.78.

I can't figure out the pattern...

 

[hilfy]

I know what's happening - your JCCP table has more than one record for some of the Company/Contract combinations in JCJM. You may be able to get around this by going to the Database menu and turning on "Select Distinct Records".

If that doesn't solve the problem, what type of section are you using for your data that has inflated numbers? Group Header? Group Footer? Details? What group levels are you summarizing data for?

-Dell

DecisionFirst Technologies - Six-time SAP BusinessObjects Solution Partner of the Year

http://www.decisionfirst.com

 

[wanzek1]

The numbers changed again but still not correct.

The numbers are in the Group Header 1, Group Footer 2, and Group Footer 1. The report is grouped first by JCJM.Job and JCCP.CostType.

 

[IanWaterman]

YOu need to find out what is causing the duplication, when you are adding JCCP you are either creating a many to many or a one to many.

One to many can be solved by adding another filter to your JCCP data or failing that using a running total which only evaluates on the change of a field or group based on data within your existing tables.

If you have a manay to many then you need to look at your table joins and see if you can reduce back to a one to many.

Ideally you need some sort of SQL editor (what is your database?) so that you can see what is happening to your dataset, crystal is not very useful at debugging the cause of duplications.

Ian