Update query to populate new field with specific ID

[josegm51]

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?
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.

 

[Michel Walsh]

You DON'T do a table, as you pointed out, it may become OUT OF DATE, so it
is not safe and there exist a far better approach: make a query and use that
query where you were planning to use the intended table.


SELECT a.*, b.securityName
FROM yourTable AS a INNER JOIN securities AS b
ON a.acctno=b.acctno


where I assumed your tables names were yourTable and securities.


Vanderghast, Access MVP