Counting Unique Customers in Query

[kmacd6951]

I have read some of the the other recent postings on this topic... just need a bit more help, as I am relatively new to this...

I see that the suggestion seems to be to use 2 queries to get down to the count of unique customers for a given situation. Not that it matters (I don't think) but in my case, I want to count how many customers submitted support issues in the last XX days. So if ABC called in 3 times, I only want to count them once.
Currently, I am exporting my Access data to Excel and using the COUNTA function, but it seems like I must be able to do this all within Access.

When you talk about 2 queries... Do I edit my current query to add in Customer Code a second time, and then do the Group by for both Customer Code columns? And then create a copy of that query, and edit it so I COUNT on the second instance of Customer Code? Cause when I tried that, it is still not counting Unique instances.

So here is the SQL from where I tried to add in the second column for CUSTOMER,and do Group By on both... Customer = SWB_SW_CUSTOMER.AMNEMONIC in this query.

SELECT SWB_SW_CASE.SWCASEID, SWB_SW_CUSTOMER.AMNEMONIC, SWB_SW_CUSTOMER.AMNEMONIC, SWB_SW_CASE.SWTYPE, Team_Type.Team_Type, SWB_SW_CASE.SWSTATUS, SWB_SW_CASE.SWDATECREATED, SWB_SW_CASE.SWDATERESOLVED, SWB_SW_PROD_RELEASE.SWNAME, SWB_SW_CASE.SWCREATEDBY, Employee_Team.TEAM, DateDiff("d",SWB_SW_CASE!SWDATECREATED,[SWDATERESOLVED]) AS CRT, SWB_SW_CASE.AISSUETYPE, SWB_SW_CASE!ATIMESPENT/60 AS Hours_Spent, SWB_SW_CASE.ATIMESPENT
FROM ((SWB_SW_PROD_RELEASE INNER JOIN ((SWB_SW_CASE INNER JOIN SWB_SW_CUSTOMER ON SWB_SW_CASE.SWCUSTOMERID = SWB_SW_CUSTOMER.SWCUSTOMERID) INNER JOIN SWB_SW_INST_PRODUCT ON SWB_SW_CASE.SWINSTPRODID = SWB_SW_INST_PRODUCT.SWINSTPRODID) ON SWB_SW_PROD_RELEASE.SWPRODRELEASEID = SWB_SW_INST_PRODUCT.SWPRODRELEASEID) LEFT JOIN Team_Type ON SWB_SW_CASE.SWTYPE = Team_Type.Type) LEFT JOIN Employee_Team ON SWB_SW_CASE.SWCREATEDBY = Employee_Team.SWLOGIN
GROUP BY SWB_SW_CASE.SWCASEID, SWB_SW_CUSTOMER.AMNEMONIC, SWB_SW_CUSTOMER.AMNEMONIC, SWB_SW_CASE.SWTYPE, Team_Type.Team_Type, SWB_SW_CASE.SWSTATUS, SWB_SW_CASE.SWDATECREATED, SWB_SW_CASE.SWDATERESOLVED, SWB_SW_PROD_RELEASE.SWNAME, SWB_SW_CASE.SWCREATEDBY, Employee_Team.TEAM, DateDiff("d",SWB_SW_CASE!SWDATECREATED,[SWDATERESOLVED]), SWB_SW_CASE.AISSUETYPE, SWB_SW_CASE!ATIMESPENT/60, SWB_SW_CASE.ATIMESPENT, SWB_SW_CUSTOMER.ASD
HAVING (((SWB_SW_CUSTOMER.AMNEMONIC)<>"PRO" And (SWB_SW_CUSTOMER.AMNEMONIC) Is Not Null And (SWB_SW_CUSTOMER.AMNEMONIC)<>"PRO" And (SWB_SW_CUSTOMER.AMNEMONIC) Is Not Null) AND ((SWB_SW_CASE.SWTYPE) Is Not Null) AND ((SWB_SW_CASE.SWDATECREATED)>=[From Date/Time:] And (SWB_SW_CASE.SWDATECREATED)<=[Through Date/Time:]) AND ((SWB_SW_CUSTOMER.ASD)=1));

 

[PaulF]

Hi

This may help. The first query would group by customer name where date is greater than you want.

The second query would be based on the first, meaning that instead of basing the query on a table, base it on the first query, again using a group by query with the count function.

If your data included, for example, state as well as customer, then the first query would be a table of the names by state, the second query would return the count of customers by state.

Hope this was clear.

Paul