I am trying to develop a query to retrieve advertisement data within SMS 2.0 from the SQL 7.0 database. I need the
Advertisement Name, the number
of successful installs of that advertisement, and the total number of machines that were originally targeted by the
advertisements' collection query.

I have found the first two without a problem but it is relating the first two to the third that is giving me problems. The
number of rows within the
_CPY_OfferManager_"#" table is the total numebr of machines targeted. The "#" target for each advertisement is held in
the CollectionOfferList.CollectionNumID

My query so far looks like this...

SELECT DISTINCT
OfferStatusSummaryDetail.ProgramSuccess,
SMSPackages.Name, OfferStatusSummary.SiteCode,
CollectionOfferList.CollectionNumID,Mnum
FROM OfferStatusIntervals INNER JOIN
OfferStatusSummaryDetail ON
OfferStatusIntervals.DisplayInterval = OfferStatusSummaryDetail.DisplayInterval
INNER JOIN
OfferStatusSummary ON
OfferStatusSummaryDetail.OfferStatusSummaryID = OfferStatusSummary.OfferStatusSummaryID
INNER JOIN
ProgramOffers ON
OfferStatusSummary.OfferID = ProgramOffers.OfferID INNER JOIN
SMSPackages ON
ProgramOffers.PkgID = SMSPackages.PkgID INNER JOIN
CollectionOfferList ON
OfferStatusSummary.OfferID = CollectionOfferList.OfferID
ORDER BY OfferStatusSummaryDetail.ProgramSuccess

This query has a couple of extra return values but it is almost able to do what I need. I am not strong enough in SQL to
get where I need to go though.

Thank you for any help.
J Perry