Access Query problem

Hello,

I'm using Access 2000 and I have a table with the following fields:

- id
- name

In my query, I want to display the values of both of these fields for each row, but also display how many times each unique name appears. For example, how many rows have the name "Bob". To do this, I'm using the COUNT function, but it's not working! Here is my SQL statement:

SELECT DISTINCT id, name, COUNT(bid) AS total FROM people;

When I run this query, I get the following error:

"You tried to execute a query that does not include the specified expression 'id' as a part of an aggregate function

Can anyone help me with this?

You can't use an aggregate function and list details at the same time. Your COUNT(bid) will return 1 for every row (id, name), and that's not what you want is it? Besides that, you need to use GROUP BY when you use COUNT(). I take it as id and name are unique, so what you might want to do is something like this:
SELECT name, COUNT(bid) AS total FROM people GROUP BY name

//NoXcuz