Need Unique Count

[kmacd6951]

I thought creating a query within a query was what I needed to do, but now I am not sure.

I run a query, entering a date range, based on multiple tables. I put the results into a Pivot Table view and move my data fields around to get a variety of totals.

For example:
Header: Team_Specialties
Column: is Customer Code
Data: is Count of Case ID's for each customer.
so:
Team_Specialties (EDI, APP, TECH)

ABC 5
GFR 3
JHU 2


I need to be able to then change and select only one of the 3 Team_Specialties (EDI, APP, TECH) at a time, and then get a count of Unique customer Codes. Right now the count I get is the # of cases for all those customers. (e.g 10, when I really want to see 3)

This is done in Excel using COUNTA function on the column of Customer Codes. How do I do this in Access?

 

[SydneyGeek]

Try using a Totals query to group the results by type.
Then base a further query on the Totals query. You should be able to use COUNT the second time around, to give what you want.

An alternative is to build a query with no more than 2 or 3 fields, one of which is Team_Specialties. Once you have the data displayed, go into SQL view.
Change the first line from SELECT ... to SELECT DISTINCT ...
This will grab the first occurrence of each different record. A simple count on these will also give the result.

Denis

 

[NateO]

Otherwise, use Group By on a field to test only a specific field in a record with Count().