count total no, of records

[ella]

i'm creating a system wherein i would like to count the number of clients each agent has. i'd like the query to count the primary key to determine this. how do i go about it? help!

 

[Jerry Stoner]

Create query, click Totals icon. In the PK column total by Count.

 

[David R]

You'll probably want to have two fields in your query (it can be the PK twice as far as I know).
One to Group By, the other to Count.

 

[Pat Hartman]

Select Agent, Count(*) as CountOfClients
From tblClient
Group by Agent;

 

[ella]

hi jerry and david,

thanks for your suggestions. i've tried doing your tips. however, my primary key is the client number that's why when i try to count them in the query, it counts the number of client numbers. do you suggest i create an agent number as well for this to work?

help! thanks so much!

 

[ella]

hi pathartman,

where shall i paste the code you've created? sorry this is the first time i'll be doing this.

thanks so much,
ella

 

[David R]

Pat's answer is the SQL version of the query. If you are in Query Design View and click on the Design/View/SQL button at the (usually) far left of your menu, you'll see something similar. It's a more flexible way to design queries.

David R

 

[Pat Hartman]

You will however, need to translate my example into your own table and column names.