Count

[Terry]

I would like to write a query or report that will show me
how many invoices each salesperson created. I have
created a query with the following fields: invoice date,
salesperson code, and invoice number. I grouped by
salesperson code and did a count on invoice number. This
created output of listing all invoices not a total number
for each salesperson. Should I be using a query or a
report for this. Can someone help me get the output I'm
trying to get? As you can tell, I'm new to Access. Thanks
in advance.

 

[PC Datasheet]

Terry,

Take the invoice date out of your query. Be sure you clicked on the Sigma
button on the toolbar at the top of the screen. Run the query and you will
get what you want.

 

[Matthew]

Think of grouping as forming a single row from a group of rows based on some
field. Note that once 'grouped' it no longer makes sense (and won't work) to
try and list invoice date, inv no, etc.
<untested example>
SELECT [salesperson code] AS Salesperson, count([salesperson code]) AS [Inv
Count]
FROM tblInvoices
GROUP BY [salesperson code];

Assuming I haven't made mistakes in above SQL (all fields are in same table
tblInvoices & and substituting your own field names, etc) you should get
output something like

Salesperson Inv Count
bj 0
pg 54
oo 159
etc.

If needbe you can also add joins, criteria, etc to further refine your
query.

 

[Terry]

Thanks for the replies. It works perfectly. You guys rock.