Easy SQL question for Access users. Help Please!

[Amaze14]

Hello, I am an Access user new to SQL and more complex queries. I am trying to create a column in my table "wins" that counts entries in "name" by unique names.

For example,

ID name wins Number
23 Joe 4 1
24 Joe 20 2
25 Mike 13 1


So, everytime the query finds a unique entry in the 'name' column the 'number' column starts back at 1.

One helpful idea for a query I received was : (SELECT wins.id, wins.name, wins.wins, DCount([id],"[wins]","name ='" & [name] & "' and id <" & [id])+1 AS [Number]
FROM wins)

I haven't been able to get the query to work though. Does anyone have any hints, new ideas, or see where I should modify this query? I do not know visual basic.

THANKS!
~Amanda

 

[Mile-O]

Why would you want a query to return such as result?

 

[ColinEssex]

Amanda - we had a similar thing the other day.

Have a look here

http://www.access-programmers.co.uk/forums/showthread.php?t=77701

my code (posted by Ally) is long winded but it does work!!

nows your chance to do some VB!!

Col

 

[Amaze14]

I am just using the field to create a component for ranking. For example, Joe with ID 23 ranks 2/2 in terms of wins for people named Joe, while Joe with ID 24 ranks 1/2 for people named Joe.

My example is strange I know, the actual data I'm working with is confidential and I am working on this sub-ranking process for an annual report. For example, I'm ranking everyone by total wins, then I'll rank total wins by name, and then maybe I'll rank wins by geography.

Thanks for your responses!

 

[Jon K]

What you received works. See query in attached database.

DCount() is inefficient. If the table is large, you may consider using VBA to add the numbers directly in the table.
.