I have a database where I have a lead record (contact or person record) which is related to an appointments table where the different appointments are stored for each lead.
Basically, 1 lead can have multiple appointments. I need to run a report based off of the appointments table, and see how many total appointments I have and then also see how many unique people are in there.
It can't be based off the leads table or else this would be really easy - it has to be based off the appointments table because it needs other information that the leads table can't access directly.
So if I have 6 appointments with 1 person and 6 appointments with another person, I need to see the table below:
|Sales Rep||Total Appointments by Sales Rep||Total Unique Leads by Sales Rep|
|Sales Rep 1||6||1|
|Sales Rep 2||6||1|
Does anyone have any hints on how I can build a calculation in the appointments table to see how many unique Lead ID's there are in a found set of appointments?
Thanks in advance,
To clarify any misunderstanding on the objective -
This is a sales report by sales rep. The sub-summary is breaking down the total number of appointments the sales rep went out on, and then I want to see how many of those appointments were a unique person and not just 5 appointments for 1 person.
So a sales rep could go out to 50 different appointment records, but the actually unique people he went to see could be 10. I need to know that. Using another sub-summary part that sorts by the lead will not work as I do not need the lead information I just need the count.
Sorry my first post wasn't clear, I didn't think of it from that angle which might have confused you.