check rows in a table in case of duplicate update the next column

[inder]

Hi Guys,



I am working on a database containing approx. 300,000 records. Some of which have been assigned same ID's. I want to go through the column id and for each duplicate value i want to add sub id in the next column. eg.

Member_ID	Sub_ID
76w3322	1
72y6860	1
72y6860	2
67g0230	1
36p7832	1
36p7832	2
36p7832	3
45h6741	1

The intent is to uniquely identify every record in the database. IS it possible through SQL queries or help me with Macro code.
My idea is:

x= first column value;
i=1;
While(End of the column){
offset to next row cell;
if(x==current row value){
i=i+1;
add value of i to next column cell in the same row
}
else{
i=1;
add value of 1 to next column cell in the same row
}
}

Help me guys with the code.......

[June7]

This is assigning a 'row number' sequence by group. This will require VBA code to save the values to table. Code would open recordset and in looping procedure read each record, increment row value until the Member_ID changes, and save to the record. Alternatively, this effect can be produced in a report by creating a Group section on the Member_ID field and having a textbox in the Detail section with RunningSum property set to OverGroup and ControlSource of =1.

Why do you need to do this?

[inder]

I am newbie in VBA and don't know much functions. Can you please provide me piece of code related to it.

[June7]

No, I don't have code. Attempt code and when you encounter issue, post question with code. Refer to link at bottom of my post for debug techniques. You need to open a recordset and loop through its records. Use a variable to hold the row number and increment it or reset back to 1 depending on the Member_ID. This is an exercise in logic and of course will need to know VBA to apply the logic. You obviously have an understanding of programming concepts, now learn VBA.

Here is an article about manipulating recordset. http://msdn.microsoft.com/en-us/libr...ffice.12).aspx