Using count in a query

[KDes via AccessMonster.com]

I have a query with customer name appearing per order number. How would I get
a count of the number of orders per customer so customer name will only
appear once and the total number of orders will appear instead of each
individual order?
KDes

 

[UpRider]

Set up a totals query with two tables, your orders table and your customer
table.
Add a join line from the [customerid] in the customer table to the
[customerid] in the orders table.
Add the [customerid] from the orders table to the grid.
Add the [customer name] from the customer table TWICE to the grid.
From the VIEW menu make sure to check Totals.
For one of the [customer name] totals row choose COUNT
For the other two columns choose GROUP BY
Run the query...

hth, UpRider

 

[KDes via AccessMonster.com]

I followed your instructions and it worked!!! Thanks!!!

Set up a totals query with two tables, your orders table and your customer
table.
Add a join line from the [customerid] in the customer table to the
[customerid] in the orders table.
Add the [customerid] from the orders table to the grid.
Add the [customer name] from the customer table TWICE to the grid.
From the VIEW menu make sure to check Totals.
For one of the [customer name] totals row choose COUNT
For the other two columns choose GROUP BY
Run the query...

hth, UpRider

I have a query with customer name appearing per order number. How would I
get
a count of the number of orders per customer so customer name will only
appear once and the total number of orders will appear instead of each
individual order?
KDes

 

[David F. Cox]

For my education: I had a problem with count once (unresolved), and solved
it using sum.

Should it always give the same result if the tables are linked as suggested,
grouped by customer name and id (in case two names the same), and have the
third field as Number of orders:1 and Sum that?

David F. Cox

Set up a totals query with two tables, your orders table and your customer
table.
Add a join line from the [customerid] in the customer table to the
[customerid] in the orders table.
Add the [customerid] from the orders table to the grid.
Add the [customer name] from the customer table TWICE to the grid.
From the VIEW menu make sure to check Totals.
For one of the [customer name] totals row choose COUNT
For the other two columns choose GROUP BY
Run the query...

hth, UpRider

 

[UpRider]

David, sum is for getting a total for a numeric field, like the sum at the
bottom of a spreadsheet column. In your case, you defined a new column with
a value of 1 and summed that. The end result is the same as Count.
UpRider

For my education: I had a problem with count once (unresolved), and solved
it using sum.

Should it always give the same result if the tables are linked as
suggested, grouped by customer name and id (in case two names the same),
and have the third field as Number of orders:1 and Sum that?

David F. Cox

Set up a totals query with two tables, your orders table and your
customer table.
Add a join line from the [customerid] in the customer table to the
[customerid] in the orders table.
Add the [customerid] from the orders table to the grid.
Add the [customer name] from the customer table TWICE to the grid.
From the VIEW menu make sure to check Totals.
For one of the [customer name] totals row choose COUNT
For the other two columns choose GROUP BY
Run the query...

hth, UpRider

I have a query with customer name appearing per order number. How would I
get
a count of the number of orders per customer so customer name will only
appear once and the total number of orders will appear instead of each
individual order?
KDes