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