Question Locked

Access: count index in a select query

0 helpful

3 replies

by lital_hess Feb 2, 2007 last reply Feb 2, 2007

Closed to new repliesThis discussion is closed. It stays readable, but it takes no new replies.

I would like to add to my query a count field, and I have no idea how....
the desired results is :

name |count
----------
xxx | 1
yyy | 2
zzz | 3

name | count
------------
zzz | 1
xxx | 2
yyy | 3

3 replies

Clarifications

select name, count(*) from table group by name

create another table put in a rownumber field with an identity property and the other(s)

insert into myrowtable select name from mytable order by name.
as each row from the select is inserted the identity field increases by 1 (default)

When you've done drop the table.

May I ask why you want this, it's a very very very bad idea to make use of it for pointing to data in the database.

PS count is the number of records so

xxx
yyy
zzz
zzz

Would give
xxx 1
yyy 1
zzz 2

This discussion is closed to new replies.