Link to home
Start Free TrialLog in
Avatar of sudif
sudifFlag for Bahrain

asked on

access 2007 distinct query problem

I want the query to return unique values for the field T_Beneficiary.Ben_CPR I tried distinct and didn't make any change.
group by doesn't work
what is the ideal solution
PARAMETERS [from] DateTime, [to] DateTime, company Text ( 255 );
SELECT CPP_Company.Cpy_Name, CPP_Company.Cpy_Adress, CPP_Company.Cpy_Tel, CPP_Company.Cpy_Fax, T_Beneficiary.Ben_CPR, T_Beneficiary.Ben_Name, T_InitialActivity.Act_Route, T_InitialActivity.Act_DtePI, [T_PI-LPOCpy2].ID_PILPO, [T_PI-LPOCpy2].PI_LPO_No, T_Pay_PI.Pay_No, T_Pay_PI.Pay_Amt, T_Pay_PI.Pay_Dte
FROM (CPP_Company INNER JOIN ((T_Beneficiary INNER JOIN [T_PI-LPOCpy2] ON T_Beneficiary.Ben_ID = [T_PI-LPOCpy2].PI_BenID) INNER JOIN T_InitialActivity ON T_Beneficiary.Ben_ID = T_InitialActivity.Act_Ben) ON CPP_Company.Cpy_ID = T_Beneficiary.Ben_Cpy) INNER JOIN T_Pay_PI ON [T_PI-LPOCpy2].ID_PILPO = T_Pay_PI.Pay_LPO_ID
WHERE (((CPP_Company.Cpy_Name)=[company]) AND (([T_PI-LPOCpy2].PI_LPODte)<[to] And ([T_PI-LPOCpy2].PI_LPODte)>[from]))
ORDER BY [T_PI-LPOCpy2].PI_LPO_No, T_Pay_PI.Pay_No, T_Pay_PI.Pay_Amt;

Open in new window

Avatar of kmslogic
Flag of United States of America image

Link to home
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Above "which will produce a list of all Ben_CPR's. " should be "which will produce a list of all UNIQUE Ben_CPR's."