Re-Sequencing

[mous]

Hello

I have a table (Access 2000) named tblStudents.

In this table there are four fields:

ID, Sequence No, GroupID, GroupCode

that contains the following information:

ABD01010101, 4, 1234, 123
AEE02020202, 3, 1234, 123
ABD01010101, 1, 5678, 567
CAA03030303, 2, 1234, 123
EEE04040404, 1, 1234, 123 etc. etc.

I want to re-sequence the Sequence No based on the Student ID in ascending order (but also taking care of the Group ID and Code)

For example, the results will be:

ABD01010101, 1, 1234, 123
AEE02020202, 2, 1234, 123
CAA03030303, 3, 1234, 123
EEE04040404, 4, 1234, 123

ABD01010101, 1, 5678, 567
and so on ...

Thanks for any help??

Dawn

 

[FoFa]

Use VBA. Query input sorted by groupid then ID, everytime the group ID changes, reset your counter to 1. Basic logic would be:

dim HoldGroupID, Cntr
HoldGroupID = 0 (or some initial value not in table)

Get first record
Do while there are rows
if Holdgroupid <> GroupID then
Cntr = 1
Holdgroupid = groupid
end if
Update Seq to Cntr
add 1 to Cntr
get next record
loop

 

[EMP]

The attached database (Access 2000) contains a query for re-sequencing.

Note: As the query uses a subquery for re-sequencing, when the students table is large, running the query may take time.

When the table is large, you will find that using VBA, as set out by FoFa, is much faster.

 

[mous]

Many Many Thanks for your replies...

Sorry, but I have taken the easy way out..

This query is just the answer I was looking for (OK it is slow but I don't care - it shall not be run that often). Thanks EMP

Pat - I should have made myself clearer. I am working with a third party SQL database that I have ODBC connections for. I have no control on the design. Thanks anyway.

FoFa - Thanks for your reply and although I understand this logic, I'm not sure how to apply it. How do I get recordsets, How to I attach SQL statement to modules? and how do I run these modules. I need to read up on this. I'm really not yet into creating modules etc.

Again, thanks for your help !!