Count Query

[darth sidious]

Hi

I have a a table 'Orders' with fields (Order Number, Order Date, CD Number, Card Number).

I would like to produce a query in access 2010 that would allow me to count how many times the CD Number 'Diab190617' has been purchased.

I would like to store the results of this count and counts on other cds numbers somewhere so that I can produce a graph/chart of these counts. How can I do this?

Any help would be greatly appreciated.

Thanks

Darth Sidious:banghead:

 

[Mile-O]

Two things:

1. You would just do a query with the totals turned on with the field you want to count. So, you'll need SQL like this.

SELECT [CD Number], Count([CD Number]) As Total
FROM Orders
WHERE [CD Number] = "Diab190617";

2. You should not store the result of your count in a table. That you can calculate it in a query at runtime is enough. Storing calculated values (without very good reason) goes against the idea of database normalisation/design.

 

[darth sidious]

what you have requested throws back the following error:

you tried to execute a querie that does not include the specified expression 'CD Number' as part of an aggregate function.

 

[Mile-O]

Forgot the GROUP BY clause.

SELECT [CD Number], Count([CD Number]) As Total
FROM Orders
WHERE [CD Number] = "Diab190617"
GROUP BY [CD Number];

 

[darth sidious]

Thanks, that worked. Much appreciated.

Darth Sidious