Link to home
Start Free TrialLog in
Avatar of Gordon Hughes
Gordon HughesFlag for United Kingdom of Great Britain and Northern Ireland

asked on

Crystal Reports Duplicating Data

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
Avatar of peter57r
peter57r
Flag of United Kingdom of Great Britain and Northern Ireland image

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.
Avatar of Gordon Hughes

ASKER

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.
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
Hi peter57r
I am on holiday for a few days will continue with this next Monday
Gordon
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
Hi
Any responses?
ASKER CERTIFIED SOLUTION
Avatar of Gordon Hughes
Gordon Hughes
Flag of United Kingdom of Great Britain and Northern Ireland image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Thanks for your input, found answer ok