You've created a report and now you've got 'duplicate records' and no matter what you've tried in the Database Expert Links dialogue the duplicates are still there. You've also checked on Select Distinct Records and they are still there.
This is usually caused by adding two or more 'details level' tables to a report. By this I mean tables that have multiple distinct records for the lowest level of grouping. For example, a Sales table may have multiple records for each customer ID and a Credit table may also have multiple records for each customer ID. When you add both of those tables to a report then you can get way more records back than you want. Usually the number of records per customer on the report will be the number of records per customer in the sales table times the number of records in the credit table.
Hopefully at this point you haven't spent too much time developing your report, as I am going to suggest that you build a new one...sorry. However, instead of using tables in your report you can try to use a Command object in the Database Expert. Another solution is to use a Sub-Report for the credit information but this can cause some extra work if you need to use the credit totals in the main report.
the following steps will be an example using the Xtreme sample database which ships with Crystal Reports. If you don't have this database Crystal Reports Step by Step Report Creation Script
Here's the scenario...you've got a report that is based on Customers & Orders & Order Details & Product & Product Type. If you go to the Database > Show SQL Query you may get something like
SELECT
`Customer`.`Customer Name`,
`Orders_Detail`.`Unit Price`,
`Product`.`Product Name`,
`Orders_Detail`.`Quantity`,
`Orders_Detail`.`Order ID`
FROM
((`Customer` `Customer`
INNER JOIN `Orders` `Orders` ON `Customer`.`Customer ID`=`Orders`.`Customer ID`)
INNER JOIN `Orders Detail` `Orders_Detail` ON `Orders`.`Order ID`=`Orders_Detail`.`Order ID`)
INNER JOIN `Product` `Product` ON `Orders_Detail`.`Product ID`=`Product`.`Product ID`
So far so good...no duplicates. Now you want to add the credit information for a customer. If you create a separate credit report using Customers & Credit, then you may have a Show SQL Query result similar to the query below.
SELECT
`Credit`.`Customer Credit ID`,
`Credit`.`Amount`, `Customer`.`Customer Credit ID`,
`Customer`.`Customer Name`,
`Credit`.`Credit Authorization Number`
FROM
`Customer` `Customer`
INNER JOIN `Credit` `Credit` ON `Customer`.`Customer Credit ID`=`Credit`.`Customer Credit ID`
What you want to do is to bring only the credit amount into your first orders report. However, if you add the Credit table to the Orders based report, you'll get duplicate orders and credit amounts and all of your orders and credit summary data will be wrong.
Now instead of using tables we are going to combine the results of the above 2 Show SQL Query results and create a Command object. If you've previously set up the Xtreme sample database along with an ODBC connection, go to File > New > Blank Report and in the Xtreme connection, select Add Command. Paste in the following syntax into the Command object.
SELECT
`Customer`.`Customer Name`,
`Orders_Detail`.`Unit Price`,
`Product`.`Product Name`,
`Orders_Detail`.`Quantity`,
`Orders_Detail`.`Order ID`,
(SELECT
SUM(`Credit`.`Amount`)
FROM `Customer` `Customer2`
INNER JOIN `Credit` `Credit` ON `Customer2`.`Customer Credit ID`=`Credit`.`Customer Credit ID`
WHERE `Customer`.`Customer Credit ID` = `Customer2`.`Customer Credit ID`
) AS CreditAmount
FROM
((`Customer` `Customer`
INNER JOIN `Orders` `Orders` ON `Customer`.`Customer ID`=`Orders`.`Customer ID`)
INNER JOIN `Orders Detail` `Orders_Detail` ON `Orders`.`Order ID`=`Orders_Detail`.`Order ID`)
INNER JOIN `Product` `Product` ON `Orders_Detail`.`Product ID`=`Product`.`Product ID`
If you look at the above syntax, the second SELECT you see is part of a sub-select statement that will bring back the credit amount from the Credit table. This technique will ensure that no duplicate orders or credit records will be created.
If you now create a group on the customer name and total up the orders you'll see that the orders are added correctly. For the credit amount, you can just put the field directly on the customer name Group Header as it's already been aggregated in the query.
If you haven't fallen asleep or lost interest at this point, here are some notes:
1) In the sub-select statement, in the FROM clause, the Customer table has been aliased as Customer2...this is so that we can later reference / equate the main query's Customer Credit ID to the sub-queries Customer Credit ID. This is done in the sub-query's WHERE clause. If you are familiar with sub-reports, think of this as linking a subreport based on a customer ID.
2) The sub-select statement can only bring back one value per row of the main query...we are using a SUM of the credit amount to ensure that we bring back one aggregated amount per row of the main query.
3) The sub-select statement can only bring back one field. You would need an additional sub-select statement for each additional field that is not part of the main query. For example you may wish to bring back the Count of credit authorizations that a customer may have had.
4) To summarize what was done...the changes that were made to the Credit SQL when creating the sub-query was the addition of the WHERE clause, using a SUM on the credit amount, putting brackets around the entire sub-select, and then assigning the final expression a name of CrreditAmount.
I hope that this helps explain what's happening when you are getting too many records when you add two or more 'details level' tables to your report. And hopefully this will help address that issue.
