Using count in a query

  • Thread starter Thread starter KDes via AccessMonster.com
  • Start date Start date
K

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
 
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 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
Click to expand...
Click to expand...
 
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


UpRider said:
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
Click to expand...
 
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

David F. Cox said:
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


UpRider said:
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 said:
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
Click to expand...
Click to expand...
Click to expand...
 
Back
Top