Counting issue in Query

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I am reposting and hopefully explaining better in hopes of getting some help.

I have a report that is based on a query that uses a form and subform. The query has a field for "Assigned Consultant" thatb I need to count entries just once for each listed Assigned Consultant. My current count field in the query counts total number even if each is listed 50 times.

Example

Bob
Bob
Bob
Glen
Glen
Sam
Count should be 3 not 6.

Below is code I have been given to try

SELECT (Count[Assigned Consultant]) FROM (SELECT DISTINCT [Assigned Consultant] FROM [Location])

I have tried placing this in the criteria field of my count field for Assigned Consultant and get The syntax of the subquery in this expression is incorrect. "Check the subquery's syntax and enclose the subquery in parentheses.

Am I placing the code in the right spot and is it correct.

I use the query count in a report field.

Any help would be greatly appreciated as always.
 
You should create a Group By Query based on the field where the consultant names are listed in.This will group the names together.
 
Back
Top