Crystal Reports Duplicating Data

Gordon Hughes
Gordon Hughes used Ask the Experts™
on
Hi
I ave a report that when I add a group it duplicates the iten detail where the item number is the same
See attached doc file

Gordon
Crystal-Reports-Duplicating-data.doc
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
The grouping has nothing to do with the 'problem'.

You will get a different result in your report depending on whether or not you include any fields from POREQHVW table.

If you don't include any fields from this table the result is based purely on the records in PURREQ - the relationship is not applied.   However, if you include any field from  POREQHVW then the relationship is applied and you then get the result based on both tables data.

What your results suggest is that there are multiple records in POREQHVW  for some/all requisition numbers.

There is nothing in your post that suggest you need the second table there at all.
Gordon HughesDirector

Author

Commented:
Hi peter57r

The screen shots are of a report I created to replicate the problem that exists in a full report
so I do need the relationship between the 2 tables
The data does exist in both tables so I guess it is retriving it for both tables
How do I cure this?
Gordon
Without more knowledge of your data I can't be sure what solution is suitable.

You could create a group on  desscriptiononpo   and then move the fields from the detail section into this group header. Suppress the detail section.
This will eliminate the problem you have described but of course it might introduce other issues.
Gordon HughesDirector

Author

Commented:
hi peter57r
I am sure you are correct about creating the group, but i have totals which i guess would still give me duplicated values
this duplication only occurs where the same item number is used for different parts and where the poreqhw is linked

gordon
Your description suggests that it doesn't matter which matching POREQHVW record you get the required data from.

If that is so, then one possible solution would be to create a sql command to get the data from table POREQHVW so that you only get one record per req number.  Then join that command to the main table instead of POREQHVW.

Select RequistionNumber, max(field1) as Mf1, max(field2) as mf2
from POREQHVW group by requisitionnumber
Gordon HughesDirector

Author

Commented:
Hi peter57r
I am on holiday for a few days will continue with this next Monday
Gordon
Gordon HughesDirector

Author

Commented:
Hi peter57r
OK back from holiday and back on the case
Have attached the full report which is causing the issue
The issue that I have is that the main data is held in the PURREQ table and the approval data is held in the REQ view when the closedate is 01/01/1900 00:00:00
If the close date is not 01/01/1900 00:00:00 then the data moves to the POREQHVW view which also contains the item details and that is why I am getting duplicated values when the same item number is used (which is allowed as part of the system)
Any suggestions would be appreciated
Gordon
Commitments-CAPEX-V3.rpt
Gordon HughesDirector

Author

Commented:
Hi
Any responses?
Director
Commented:
I think I have solved the problem by adding a link on the description field
Seems to work now
Gordon HughesDirector

Author

Commented:
Thanks for your input, found answer ok

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial