Count Dup based one Mulitple Criteria

[SORI]

Hello everyone, not so new to Access I understand the capacity of the program. Not so good with SQL. With that said I want my query to count only records that are not duplicated base on a set of criteria.

I have a table with customer IDs and Call dates and the employee ID. I want a count of the call dates for each customer ID for each employee. However I only want to count as one occurrence if multiple calls happened on the same day. For example

Cust_ID Call_Dt
12315 8/1/2013
12315 8/1/2013
12315 8/1/2013
14253 9/1/2013


Customer ID 12315 would only count a 1 call entry instead of 3 for the date of 8/1/2013

I am familiar with dcount but I don't think this can help.

Thanks!!!:banghead:

 

[ByteMyzer]

You can try something like the following (substitute highlighted text with actual table/field names):

SELECT T1.[B][I]EmpID[/I][/B], Count(*) AS CallDateCount FROM (
    SELECT DISTINCT [B][I]EmpID[/I][/B], [B][I]Cust_ID[/I][/B], [B][I]Call_Dt[/I][/B]
    FROM [B][I]MyTable[/I][/B]) AS T1
GROUP BY T1.[B][I]EmpID[/I][/B];

 

[SORI]

Thanks! it worked just as I wanted. But I want to get even dirtier.

Say I throw in another column "Call_Channel" I want to only count the calls made face to face.

Should I run a query separate by this criteria and then run the expression above on said query?

SELECT T1.Emp_ID AS Expr1, Count(*) AS CallDateCount
FROM (SELECT DISTINCT Emp_ID, Cust_ID, Call_Dt
FROM Call_Details) AS T1
GROUP BY T1.Emp_ID;

 

[ByteMyzer]

Actually, you would create a modified version of the query, something like the following:

SELECT T1.EmpID, Count(*) AS CallDateCount FROM (
    SELECT DISTINCT [B][I]EmpID[/I][/B], [B][I]Cust_ID[/I][/B], [B][I]Call_Dt[/I][/B]
    FROM [B][I]MyTable[/I][/B]
    [COLOR="Red"]WHERE [B][I]Call_Channel[/I][/B] = 'F2F'[/COLOR]) AS T1
GROUP BY T1.[B][I]EmpID[/I][/B];