Wrong data when joining multiple tables

[bboy27]

I had a problem recently with joining tables and getting incorrect data being displayed because of the join.
I got a solution from synapsevampire who suggested I us UNION ALL to put all the data in one table. That solution worked.

The problem is I have a new situation where I have several tables that are joined displaying project information e.g actual hrs, cost, revenue etc which are being drawn from different tables and I want to add one more which is the budgeted cost for the project. The problem is when I do the join of the budget table I get the same problem of the numbers being far larger than they are for the cost field in the budget table only. I assume this is the same problem as before with the join.

Any suggestions on a solution?

 

[kskid]

It would be helpfull if you provided more technical information such as

CR Version
Database and connectivity
Sample data
What you are getting and what do you want on the output.

In addition, how are your tables linked or post the Show SQL Query so we can see how you are linked.

Cheers,
-LW

 

[bboy27]

My apologies!

The crystal version I am using is Crsytal 9 and the database is OLE DB using SQL.
Quite simply lets say I have actual hrs and am driving cost from those hrs and all I want to do is add an additional column in the cross tab from another table which shows the budgeted hrs or total cost amount from the budget table.

Before After
Actual Cost Rev Actual Budget Budget Cost Rev
Hrs hrs hrs Cost

Actaul, Cost, Rev from Actual Table and Budget hrs/Cost from Budget table. I am able to add the columns but the budget hrs figures are off not to sure why. If I put the budget seaparate with no join it works.
Any suggestions?

 

[dbpr]

This sounds familar, but its a little difficult to diagnose exactly. Crystal has problems with complicated joins and mainly when criteria is put on a secondary table and none on the primary/root table.

In these cases it goes a little mad and collects too much data then 'forgets' to filter the information. Version 8.x had loads of problems, but I am seeing similar issues with 9 & 10 (not tried XI yet). Database backends didn't make any difference although MS Access and ODBC sources caused most problems.

OK, enough history.

One way around was to use sub reports (not easily possible in a cross-tab). Or better, try to rely on SQL Server's capabilities. If you are allowed, attach the report to a view or stored proc and hide the complex join. This can be a lot faster as SQL Server will deal with the job of figuring out how to retrieve the data.

Otherwise set the report to use as many server side options as possible so Crystal tries to pass the SQL statement straight through. A warning, sometimes Crystal gets the syntax a bit off and SQL Server will kick it straight back.

Hope this helps.