Unique Records in a Query

[Jeff]

Hi All

I am trying to write a query that will tell me how many unique activities a
rep has made and I am having some problems - here is the situation:

I have a table that stores activities from my sales reps
The table has these fields:
CampaignRecordID
BillToNum
ShipToNum
ContactNum
The reps add activities to a campaign record or to a client in our
database - the record in the Activity table stores all of the information
and all of the activities the reps make
I can write a query that will tell me how many activities a rep made each
day
But I am trying to write a query that will tell me how many activities they
made to Unique contacts each day - so if they made 5 activities to the same
CampaignRecordID or same BillTo I only want to count that as one.
I guess I am either trying to suppress duplicates if that is possible or
write some other type of query that will allow me to include only unique
contact.

I am not sure if I have expressed myself enough here - please let me know if
you need any more information and thanks in advance for anyone's assistance
with this.

Jeff

 

[Damian S]

Hi Jeff,

I think you are asking how to count the number of unique activities by a
rep. You might need a couple of queries here.

Query1 - select distinct REP, CampaignRecordID from TABLE where WHERECLAUSE
Query2 - select REP, count(CampaignRecordID) from Query1 group by REP

Query1 will return the unique CampaignRecords from TABLE using WHERECLAUSE
that would need to select the date you want. This assumes also that you have
some table that you can get REP out of (Rep Details).

Hope that helps.

Damian.