1. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Feb 2002
    Rep Power

    Access Query problem


    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?
  2. #2
  3. Wiking
    Devshed Expert (3500 - 3999 posts)

    Join Date
    Sep 2000
    Rep Power
    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

    UN*X is sexy!
    who | grep -i blonde | date; cd ~; unzip; touch; strip; finger; mount; gasp; yes; uptime; umount; sleep

IMN logo majestic logo threadwatch logo seochat tools logo