Duplicate records in Crystal Reports 7 for Pervasive SQL (PSQL 11)

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?
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Bill BachPresident and Btrieve GuruCommented:
If the data was damaged in some way, then I would have expected you to find duplicate records within one or more of the individual tables.  Since you confirmed that this was NOT the case, I do not suspect data corruption at all. Instead, this looks like a SQL query issue.

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.
Mike McCrackenSenior ConsultantCommented:
Can you click to select DISTINCT RECORDS in the DATABASE menu?

BobJohnson66Author Commented:
The big question is why now?  And why just this one item?  This report has been running in its current state for literally years before this popped up.   I found an old whitepaper [https://archive.sap.com/documents/docs/DOC-21403] from SAP titled " How to troubleshoot duplicate records from displaying on a report".  I will try to work on this over the weekend.  My plan is to create a formula field that combines the Sales Order Number and SO Line Number since that is what makes the detail unique.   Next I will create a subgroup on that formula field.  Then I should be able to check the box to suppress duplicates.  I hope.
Bill BachPresident and Btrieve GuruCommented:
Answering those questions requires the aforementioned data, of course.

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
BobJohnson66Author Commented:
Technically, this is still unresolved.  However, I spoke to our ERP provider and determined that this is a one off situation at this time that will clear through by the end of the month.  If this type of situation recurs, I will dig into it further.
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today

From novice to tech pro — start learning today.