Without knowing the relationships of your tables (i.e. one-to-one vs. one-to-many vs. many-to-many), try the following code.

SELECT RMPROP.RMPROPID

,PROPNAME

,AVB_GMSCM.PDSPDID

,AVB_PDSPD.VPID

,AVB_VICEPRES.SVPID

,AVB_SVICEPRES.EVPID

,AVB_EVICEPRES.OWNID

,UNITS = COUNT(UNIT.UNITID)

,INITIALVISIT = COUNT(DISTINCT NM.NAMEID)

FROM RMPROP

JOIN AVB_GMSCM ON RMPROP.GMSCMID = AVB_GMSCM.GMSCMID

JOIN AVB_PDSPD ON AVB_GMSCM.PDSPDID = AVB_PDSPD.PDSPDID

JOIN AVB_VICEPRES ON AVB_PDSPD.VPID = AVB_VICEPRES.VPID

JOIN AVB_SVICEPRES ON AVB_VICEPRES.SVPID = AVB_SVICEPRES.SVPID

JOIN AVB_EVICEPRES ON AVB_SVICEPRES.EVPID = AVB_EVICEPRES.EVPID

JOIN UNIT ON RMPROP.RMPROPID = UNIT.RMPROPID

JOIN NAME ON NAME.RMPROPID = RMPROP.RMPROPID

JOIN RMACTION ON RMACTION.NAMEID = NAME.NAMEID

JOIN PROSPECT ON PROSPECT.NAMEID = RMACTION.NAMEID

WHERE RMPROP.RMPROPID IN ( @PROPERTIES )

AND NAME.ACTCODE = 'WI'

AND RMACTION.ACTDATE BETWEEN @BEGIN AND @END

GROUP BY RMPROP.RMPROPID

,PROPNAME

,AVB_GMSCM.PDSPDID

,AVB_PDSPD.VPID

,AVB_VICEPRES.SVPID

,AVB_SVICEPRES.EVPID

,AVB_EVICEPRES.OWNID ;

If this query blows out your count of "Units", then you may need to consider using either derived tables, a CTE, or temp tables, before piecing all of the data together in a final result set.

--pete