Update Table for Same Account Number

[mlr0911]

Hello All,

I am trying to make an Update Query that will update a table that has the same account numbers and assign them a value....ie.1,2,3,4 and so on. Does anyone know how I can do that?

Example:
Accout Num Assigned Value
12345 1
12345 1
12345 1
12544 2
12544 2
12568 3
12569 4

 

[Brianwarnock]

I am assuming that the assigned value starts as 1 with the lowest account and rises by one for each change with the ascending order order of the accounts
I presume you could do it by code.

else
create a temptable containing Account Number; and Assigned value as an autonum save but add no data.
query1 runs against your original table and has only the account num field group by and is an append query to temp table.
query2 joins the tables as originaltable Left Join to temptable on account num, select your fields and it should be done.

brian

 

[Brianwarnock]

Had to dash off last night and realsed I had not completed the story.
We are at the stage where query2 could be run as a make table query if originaltable did not have an Assigned Number field, BTW it is bad practice to have blanks in object names be they fields tables reports etc , it causes syntax problems.
If the field exists and you want to update it then query2 will be an update query, update originaltable.[assigned number] = temptable.[assigned number] and in the crieria of Account Number originaltable.[account number]= temptable.[account number]
in sql
UPDATE originaltable LEFT JOIN temptable ON originaltable.[account number] = temptable.[account number] SET originaltable.[assigned number] = temptable.[assigned number]
WHERE originaltable.[account number] = temptable.[account number];

Brian

 

[mlr0911]

Thanks Brianwarnock, I will work on this to see if I can get it to work. Thanks for all your help.