numbering sequentially by several sorted fields

[6ftAndClean]

I have a table (50,000 records) that has data in it that follows the form below. Field A is the main identifier of blocks of data. Field B identifies sub-sets of the data. Field C then gives the number order of data within the sub-sets. Field D represents what I need to achieve: it provides an index to the order of the data overall for each single identifier in field A.

A B C D
1 1 1 1
1 1 2 2
1 1 3 3
1 2 1 4
1 2 2 5
1 3 1 6
1 3 2 7
1 3 3 8
1 3 4 9
2 1 1 1
2 1 2 2
2 1 3 3
2 2 1 4
2 2 2 5

The problems are these (you just knew that here would be some!);

- the data in Field A is in fact a 9 digit number and does not number sequentially (there are many gaps in the numbering sequence)

- the numbers in Fields B & C do number sequentialy from 1 (up to a maximum of 200) as per above example.

- Cruicially Field D MUST number from 1 upwards without any break in numbering for each distinct value in field A (as illustrated above).

I have no idea how to go about achieving this. Do I do this via a query, macro or what? I have a fair idea what shape the coding could take to do this but insufficient experience and skills to actually achieve it. Any help would be very gratefully received.

Nick.

 

[BNPMike]

We probably need to know a lot more about the context eg how does data get into this table.

In the meantime. If you create a new table and load it from the existing table (append query sorted on fields 1,2,3), and if field D is an autonumber, you will get automatic numbering from 1 upwards.

 

[6ftAndClean]

The data has been poached from another database and so comes in the format as described. There is not an alternative source for this data. [Field A identifies individual streets. Field B represents sub sections of a street. Field C represents sequential points in a vector line that describe the sub section of the street.]

What you suggest in the second part of your reply is precisely what I would like to do BUT what I required was for the sequential numbering to restart from 1 for each new number in Field A. Fortunately for us both, I have realised that starting from 1 is not critical. What is critical is that the numbering is sequential with no breaks in numbering.

Providing that the autonumbered data fits nicely into the mapping software that has to interpret it tomorrow morning then hopefully that will be the end of this thread (although I would have been interested to know the answer I 1st sought). [I should end up with street center lines plotted for all the roads in the area within the control of the council I work for.]

Thanks for replying to this thread. Either way, your answer provided the solution I required! Thanks.