Data Duplication in Fields When Querying Multiple Linked Tables

[mapsreport]

In order to create two reports which contain anywhere from to 20 elements, I have to link anywhere from six to eight different tables from a database that resides on a SQL server. I need a distinct count per service per individual per local area per region etc. Most of the fields that I am using for the report are formulas. While I'm getting data counted on my report, I'm getting the same data being counted several times as it reads through all the different tables or services. For example, Ctc with office by Person A occurs once, but Person A received two services on this table and two services from another table, etc. My query will show Person A at least 5 or more times and indicate that Person A contacted the office five times (vice one), and received 4 services form one table (vice 2) etc. How do I get out of this reporting loop? The Link option does not let me access the lower left hand box for linking two files when I use this database.

 

[kenhamady]

That is because the lower left options can't be interpreted by SQL based databases.

In your situation, every one-to-many relationship will multiply by every other one-to-many to give you a greatly inflated data set.

If you are good with SQL you can do a UNION Query in your database that appends one table's records onto the next, and then run the report off of that query/view/sp.
Otherwise you can use subreports to pull data from the other tables without inflating the original report. Ken Hamady

http://www.kenhamady.com/

Crystal Reports Training/Consulting and a
Quick Reference Guide to VB/Crystal (including ADO)