>All,
>
>I am writing a building inspection application.
>
>Each DISTICT has one or more BUILDINGS
>Each BUILDING has one or more ADDITIONS
>Each ADDITION has one or more INSPECTIONS
>Each INSPECTION has one or more INSPECTIONCOMPONENT
>Each INSPECTIONCOMPONENT has one or more INSPECTIONCOMPONENTITEM
>Each ITEM has one or more INSPECTIONCOMPONENTITEM
>
>I need to sum the extension of the INSPECTIONCOMPONENTITEM quantity and the ITEM unit cost for a specific building using the following:
>
>SELECT ;
> SUM(SFAInspectionComponentItem.iQuantity * SFAItem.yUnitCost) AS iTotal;
>FROM ;
> sfa!sfainspectioncomponent ;
> INNER JOIN sfa!sfainspectioncomponentitem;
> INNER JOIN sfa!sfaitem;
> INNER JOIN sfa!sfainspectioncomponent;
> INNER JOIN sfa!sfaaddition;
> INNER JOIN sfa!sfabuilding ;
> ON Sfabuilding.iid = Sfaaddition.ibldgid ;
> ON Sfaaddition.iid = Sfainspection.iadditionid ;
> ON Sfainspection.iid = Sfainspectioncomponent.iinspectionid ;
> ON Sfainspectioncomponent.iid = Sfainspectioncomponentitem.iinspectioncomponentid;
> ON Sfaitem.iid = Sfainspectioncomponentitem.icomponentitemid ;
>WHERE Sfabuilding.iid = ?vp_iBuildingId ;
>INTO CURSOR REX
>
>Unfortunately the total is double what it should be. In my example there are 2 COMPONENTS for the INSPECTION and this seems to lead to a complete duplication of all the amounts.
>
>Since I am using a SUM, I cannot use DISTINCT is the SQL.
>
>How can I get the data not to duplicate?
>
>TIA
>
>Rex
Rex,
Isn't there a typo in code ? Second "ON" seems to be causing the problem.
Cetin