I have a custom report that I created several years ago using data from my ERP system (DBA/Evo~ERP). Just recently, I have an anomaly that has surfaced. I have manually stepped through the linked data files and the duplication does not exist in the data. The odd thing is that it only seems to affect a single part number in our item master.
I found a link to a whitepaper from Crystal Decisions titled "How to troubleshoot duplicate records from displaying on a report" that was written for Crystal Reports 9 but says it is applicable to earlier versions as well. After reviewing, I am inclined to think that this is a data corruption problem. Is there any way to detect such corruption and repair it?
Whenever you join two or more files, you can get duplication of data. This comes in because the SQL language deals with SETS of data, not individual records. When SQL Executes a JOIN, you are asking it to determine the intersection of one set of records to another. If you join in the wrong direction, or if you join to a potential duplicate key instead of a primary key, or if you have a one-to-many relationship between the tables, then duplicates can (and will) occur in the resulting data set. For a more thorough explanation, you can do a web search on "duplicate records appear in sql query results", and you'll find several decent examples which explain the issue in a lot more depth.
To truly understand what is happening, we'd need to see the query, the query results, the relationships between the tables, the source data set for a duplicate row.