Tricky query?

[oticonaus]

I have a table with

ProspectNum,
EntryNo,
Date,
Campaign.

Each prospectnum might have several records for any given campaign.

I need to work out by month, how many records there are for each campaign, counting multiple records for one campaign for one ProspectNum as 1.

On other words

ProspectNum Campaign Entry Date
100 PA0100 11054 Jan
100 PA0100 11055 Jan
100 PA0100 11056 Jan
101 PA0100 11057 Jan
100 PA0101 11058 Feb
101 PA0101 11059 Feb
102 PA0102 11060 Feb

From this data, I want to show that Campaign PA0100 has 2 unique ProspectNum's, PA0101 has 2 unique ProspectNum's and PA0102 has 1 unique ProspectNum.

I have been trying to do this in a pivot table without any luck. I am happy to do it as a query as well.

 

[PHV]

Something like this ?
SELECT D.Campaign, D.Date, Count(*) AS CountOfProspect
FROM (SELECT DISTINCT A.Date, A.Campaign, A.ProspectNum FROM tblCampaign A) D
GROUP BY D.Campaign, D.Date;

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