Number multiple records ?

[Bieomax]

Good morning

I've been doing some searching and I'm struggling finding a way to number multiple records, and so I'm just wondering if anyone has any suggestions

in effect I have a large table full of data for multiple ID's with multiple dates which I'm wanting to split down, by ID in ascending date order. So what I want to do is a number the 'duplicate' ID's like exampled in the [ID_number] field below

eg


[ID] [Date] [Result] [ID_Number]
1 01/02/2010 xxx 1
1 02/03/2010 xxx 2
1 10/06/2010 xxx 3
2 01/01/2010 xxx 1
2 01/02/2010 xxx 2
3 01/01/2010 xxx 1
3 09/01/2010 xxx 2
3 02/03/2010 xxx 3
3 12/08/2010 xxx 4
4 01/02/2010 xxx 1

I'd have used a countif if I was using excel but the table extremely out strips excel use, but to do this in access fox's me slightly.


Anyhow I hope someone can help

Thanks

Mark.

 

[jackd]

Are you really saying these are NOT duplicates? You need to keep the records, but need a way to uniquely identify each record. Perhaps you should research Primary Key, if that's the case.
Otherwise, you could write the records in the sequence you wish to a new table and create an Id field that is Autonumber. That would uniquely identify records in the table.

More info needed if I've missed your intent.

 

[Bieomax]

aye they are not duplicates, they are entries for accounts - ID being the account number and the Date being a transaction date

and so I wanted to add a number for each transaction date (Date) that an account (ID) had

so account "1234" (ID) had 3 transactions (DATE) so the earliest date would be numbered 1 latest would be numbered 3

So I can then start to split the table into smaller tables so all the first transactions of each account being on one table then all the 2nd on the next table and so on and so on.

As at the moment I have everything on a single table (1mil+ rows) and each account having anywhere from 1 to 8 transactions, but its all mixed in on 1 table at the moment

does this expand enough around what I'm trying to do ?

 

[Bieomax]

Sorted it myself now, i just created a number of tables and used the MIN on the group options to appended into a table then then removed those which were on the new table.

So eventually the main table wittled its self down into 1st transaction, 2nd . . . etc etc etc