How do I update table with new incrementing ids per each account

[Jose G Morales]

I have a table with AcctNo, SecurityName, otherfields.
I need to update this table with a new field that would indicate for each
AcctNo row the number of SecurityName. for example:
ACCTNO NEWFIELD SECURITYNAME
10021 1 IBM
10021 2 SEARS

10022 1 MICROSOFT
10022 2 RIMM
10022 3 GMC

Is there a function that can do this? I would appreciate any help.

 

[John W. Vinson]

I have a table with AcctNo, SecurityName, otherfields.
I need to update this table with a new field that would indicate for each
AcctNo row the number of SecurityName. for example:
ACCTNO NEWFIELD SECURITYNAME
10021 1 IBM
10021 2 SEARS

10022 1 MICROSOFT
10022 2 RIMM
10022 3 GMC

Is there a function that can do this? I would appreciate any help.

What's the logic for assigning the numbers? Why is RIMM 2 and GMC 3 rather
than vice versa? What happens to [NEWFIELD] when you delete or add a record to
the table?


John W. Vinson [MVP]

 

[Jose G Morales]

There is no logic, this is a one time deal, I intend to create a new table
with the results, at a later point I may have to refresh the data at that
point I will redo the table. I need to run some stats for each Account and
this is the only way I can think of doing it. I am trying to create a
recordset that will give me the first row for each account, then a second
that will give me the second row, then the third and so on, I know how many
rows each account has, but when I run the second query how do i filter out
the first row?, this is why if I append each row with a number this will make
this posible.

I have a table with AcctNo, SecurityName, otherfields.
I need to update this table with a new field that would indicate for each
AcctNo row the number of SecurityName. for example:
ACCTNO NEWFIELD SECURITYNAME
10021 1 IBM
10021 2 SEARS

10022 1 MICROSOFT
10022 2 RIMM
10022 3 GMC

Is there a function that can do this? I would appreciate any help.

What's the logic for assigning the numbers? Why is RIMM 2 and GMC 3 rather
than vice versa? What happens to [NEWFIELD] when you delete or add a record to
the table?


John W. Vinson [MVP]