Complex Query on a one-to-many-to-one table relationship

[gwoman]

The end result of the following query & subquery have mulitple records as there is a one-to-many-to-one relationahip between the tables. Does anyone see where I am missing some criteria to prevent this from happening?
Thanks in advance ...
gwoman



PARAMETERS [Forms]![150_Objects_frm]![cboObjectTypes] Long;
SELECT DISTINCTROW[150gh_Object_Property_subqry].CIF_OBJT_ID, [150gh_Object_Property_subqry].CIF_OBJT_NME, [150gh_Object_Property_subqry].CIF_OBJT_TYPE_ID, [150gh_Object_Property_subqry].CIF_OBJT_TYPE_DESC, ETLCORE_CIF_OBJT_PPTY_TYPE.CIF_OBJT_PPTY_TYPE_ID, ETLCORE_CIF_OBJT_PPTY_TYPE.CIF_OBJT_PPTY_TYPE_DESC, [150gh_Object_Property_subqry].CIF_OBJT_PPTY_VALU, [150gh_Object_Property_subqry].CIF_OBJT_TYPE_PPTY_TYPE_ID
FROM 150gh_Object_Property_subqry RIGHT JOIN ETLCORE_CIF_OBJT_PPTY_TYPE ON [150gh_Object_Property_subqry].CIF_OBJT_PPTY_TYPE_ID = ETLCORE_CIF_OBJT_PPTY_TYPE.CIF_OBJT_PPTY_TYPE_ID
WHERE ((([150gh_Object_Property_subqry].CIF_OBJT_TYPE_ID)=[Forms]![150_Objects_frm]![cboObjectTypes]) AND (([150gh_Object_Property_subqry].CIF_OBJT_PPTY_VALU) Is Null));



SELECT ETLCORE_CIF_OBJT.CIF_OBJT_ID, ETLCORE_CIF_OBJT.CIF_OBJT_NME, ETLCORE_CIF_OBJT.CIF_OBJT_CTNR_ID, ETLCORE_CIF_OBJT_PPTY.CIF_OBJT_PPTY_ID, ETLCORE_CIF_OBJT_PPTY.CIF_OBJT_PPTY_VALU, ETLCORE_CIF_OBJT_TYPE.CIF_OBJT_TYPE_ID, ETLCORE_CIF_OBJT_TYPE.CIF_OBJT_TYPE_DESC, ETLCORE_CIF_OBJT_TYPE_PPTY_TYPE.CIF_OBJT_TYPE_PPTY_TYPE_ID, ETLCORE_CIF_OBJT_TYPE_PPTY_TYPE.CIF_OBJT_PPTY_TYPE_ID
FROM (ETLCORE_CIF_OBJT_TYPE INNER JOIN (ETLCORE_CIF_OBJT LEFT JOIN ETLCORE_CIF_OBJT_PPTY ON ETLCORE_CIF_OBJT.CIF_OBJT_ID = ETLCORE_CIF_OBJT_PPTY.CIF_OBJT_ID) ON ETLCORE_CIF_OBJT_TYPE.CIF_OBJT_TYPE_ID = ETLCORE_CIF_OBJT.CIF_OBJT_TYPE_ID) INNER JOIN ETLCORE_CIF_OBJT_TYPE_PPTY_TYPE ON ETLCORE_CIF_OBJT_TYPE.CIF_OBJT_TYPE_ID = ETLCORE_CIF_OBJT_TYPE_PPTY_TYPE.CIF_OBJT_TYPE_ID;

 

[PHV]

Just a note, have you tried DISTINCT instead of DISTINCTROW ?

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244