How to assign a row a number? Help please!

[Myriad_Rocker]

Hello! I'm trying to assign a number to a set of rows so I can, essentially, create a dual primary key on a table. Below is an example of what I have...

654321 | Course 1
654321 | Course 2
123456 | Course 1
123456 | Course 2
123456 | Course 3
123456 | Course 4
123456 | Course 5

The numbers are just "student numbers" if you will. Something that identifies them. The second column is basically the name of a course. What I would like to do is number each row, but only within the group of that individual.

Example:

1 | 654321 | Course 1
2 | 654321 | Course 2
1 | 123456 | Course 1
2 | 123456 | Course 2
3 | 123456 | Course 3
4 | 123456 | Course 4
5 | 123456 | Course 5

Can I accomplish this?

 

[carash77ash]

Try creating two tables one for students and one for course. Then create a table for both information setting the relationships you will then be able to set a primary key on both

Ash

 

[SammyJ]

It isn't easy to get a number like that a query, no. Do you want it stored permanently in a table? Or visible on a report?

 

[Myriad_Rocker]

It isn't easy to get a number like that a query, no. Do you want it stored permanently in a table? Or visible on a report?

Yes, I would like for it to be stored on the table permanently...

 

[Mile-O]

What purpose would this serve?

 

[SammyJ]

That's a good question, as it isn't an easy task. We either need to use VB code or to use a query trick which won't really work for even medium size datasets.