Crystal Database Link Duplicate Fields Returned ?

[DyerQ]

Hello,

I have a query situation similar to the one below.
The issue is I would like to return only one record for
each exeption number dbo_V_CXP_CUSTOMER_PXP.QXP_EXCEPTION_NO
but I also need to return a component number in the record
but the table that contains the component number could
have several components that match the exception number table
that it is joined with. I only care about returning one component
description for my report for each exception. The join is creating several records for each exception number if there were multiple components involved with an exception number. I am unable to create varibles so that I can do a sum to correctly count the number of exceptions
that match certain conditions as the query is returning muliptle records for some exceptions if multiple components records
were entered in the table I am linked to.

Is there a way to join the tables so that

Presently
Query Link Q Yeilds Need to Yeild
Table1 Table II X---- XXX X---XXX
X--------XXX X---- XXX
XXX


SELECT dbo_V_CXP_CUSTOMER_PXP.QXP_EXCEPTION_NO, dbo_V_CXP_CUSTOMER_PXP.QXP_REPORT_DATE, dbo_CAH_V_EPL_LOT_QTY.EPL_LOT_NUMBER, dbo_V_CMP_COMPONENT.CMP_COMPONENT_CODE
FROM (dbo_V_CXP_CUSTOMER_PXP INNER JOIN dbo_CAH_V_EPL_LOT_QTY ON dbo_V_CXP_CUSTOMER_PXP.QXP_ID = dbo_CAH_V_EPL_LOT_QTY.EPL_QXP_ID) INNER JOIN dbo_V_CMP_COMPONENT ON dbo_CAH_V_EPL_LOT_QTY.EPL_QXP_ID = dbo_V_CMP_COMPONENT.CMP_CXP_ID
WHERE (((dbo_V_CXP_CUSTOMER_PXP.QXP_REPORT_DATE)>#6/1/2011#));

 

[lbass]

You can either group on the value that is duplicating and then use running totals that evaluate on change of group or you could remove the table that is causing the duplication and add it back in a linked subreport if its purpose is only to display a description.

-LB