Return sequential "Group Number" in select

Question

Return sequential "Group Number" in select

golfer

SSC-Addicted

Points: 431
More actions
September 14, 2005 at 9:52 am

#91290

I am trying to return a unique number for each group in a select. I have seen solutions for returning row numbers but what I need is a bit different. I need sequential numbers but they will be the same within the group (same GroupId).

GroupId       Name
1023           Bob
1023           Jim
1030           Joe
1030           Jeff
I want to return
groupNo    GroupId    Name
1              1023       Bob
1              1023       Jim
2              1030       Joe
2              1030       Jeff

Thanks in advance for your help

Viewing 10 posts - 1 through 9 (of 9 total)

You must be logged in to reply to this topic. Login to reply

Ninja's_RGR'us SSC Guru Points: 294069 More actions · Answer 1

I think you should do that application side. But for the fun of it :

Start with a derived table that has the rownumber and groupid in it. Then join that table to that select on the group id.

golfer SSC-Addicted Points: 431 More actions · Answer 2

Im actually going to create a query in MS Access and I need to be able to edit the result set.

I dont see how a derived table will solve my problem because I still have the issue of assigning the SAME rownumber to all rows in the same group.

Thanks for your time

golfer SSC-Addicted Points: 431 More actions · Answer 3

Sorry I misunderstood your suggestion. I could put unique GroupIds in a table with a rowcount for each and then Join that to the original table. This will work but I wonder if anyone knows a way to return it all in one select on the original table without creating this second table.

Thanks

Ninja's_RGR'us SSC Guru Points: 294069 More actions · Answer 4

Keep thinking about it. I used the word derived table.

AJ Ahrens SSC-Insane Points: 20676 More actions · Answer 5

I guess that I am being a little dense today. IF you ALREADY have grouping already established WHAT is the purpose of "making up" GROUP 1, 2, etc... when based off your example you already have GROUP 1023, 1030, ....

Good Hunting!

AJ Ahrens

webmaster@kritter.net

golfer SSC-Addicted Points: 431 More actions · Answer 6

Well I didnt want to bother with too much detail but since you have asked. I will be taking the group number MOD 2 which will give me a 0 or 1. I will then use this to alternate the backgound colors in an access datasheet for each new grouping using conditional formatting. I coul have taken the GroupId and MODded that by 2 but there are gaps in the numbers which means I get blocks of the same color that have different GroupId's.

This is for readability only.

ricva Default port Points: 1404 More actions · Answer 7

is this all done in Access? If so, are you familar with modules and calling the functions in the queries or reports?

Ric

Ninja's_RGR'us SSC Guru Points: 294069 More actions · Answer 8

That's why I always ask why the heck do you want to do that for... now I'll rememeber to never forget to ask that again .

noeld SSC Guru Points: 96590 More actions · Answer 9

The alternate background is very easy to create by simply specifying in the format event the background color. In your case all you need to do to keep a boolean variable that alternates per group like:

bGray = not bGray and depending on the result you alter that baground color

Nothing to do on the SQL side!

Cheers

* Noel