Is updating names in a table easier with a query or code?

[irethedo]

I have a table that contains a field for office location and a field for a Computer Name
of a PC in each office. (BTW: this table does not have auto-number index field)

[pre]Location ComputerName
Chicago Office AS400 Server
Chicago Office Office Dell PC
Chicago Office Office Dell PC
Chicago Office Sparc Station
Dallas Office AS400 Server
Dallas Office Office Dell PC
Dallas Office Office Dell PC
Dallas Office Office Dell PC
Dallas Office Sparc Station
Memphis Office AS400 Server-
Memphis Office Office Dell PC
Memphis Office Office Dell PC
Memphis Office Office Dell PC
Memphis Office Sparc Station
Seattle Office AS400 Server
Seattle Office Office Dell PC
Seattle Office Office Dell PC
Seattle Office Sparc Station[/pre]


I would like to give each computer a unique name by adding a sequencial
number behind it in the table:

[pre]Location ComputerName
Chicago Office AS400 Server-1
Chicago Office Office Dell PC-1
Chicago Office Office Dell PC-2
Chicago Office Sparc Station-1
Dallas Office AS400 Server-2
Dallas Office Office Dell PC-3
Dallas Office Office Dell PC-4
Dallas Office Office Dell PC-5
Dallas Office Sparc Station-2
Memphis Office AS400 Server-3
Memphis Office Office Dell PC-6
Memphis Office Office Dell PC-7
Memphis Office Office Dell PC-8
Memphis Office Sparc Station-3
Seattle Office AS400 Server-4
Seattle Office Office Dell PC-10
Seattle Office Office Dell PC-9
Seattle Office Sparc Station-4[/pre]

Can this be easily done by a query or can it only be done within
an event procedure?

Thanks

 

[MajP]

You need to first add an auto number to the table. Then you can run a make table query to create a new table.

SELECT 
 A.autoID, 
 A.Location, 
 A.ComputerName, 
 (select count(*) from tblComputers as B where B.AutoID < A.autoID and A.ComputerName = b.computerName)+1 AS Rank,  A.computerName & " - " & [Rank] AS NewName 
 INTO newtblComputer
FROM tblComputers AS A;

Replace your old table with the new table and change field names as needed.

 

[irethedo]

Thank you for an easy solution MajP!