Gordon Hughes
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
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
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
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.
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.
ASKER
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
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
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
ASKER
Hi peter57r
I am on holiday for a few days will continue with this next Monday
Gordon
I am on holiday for a few days will continue with this next Monday
Gordon
ASKER
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
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
ASKER
Hi
Any responses?
Any responses?
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Thanks for your input, found answer ok
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.