I now have a solution using Tools > SQL…
I start with the unsorted “Data” table. For me it is just a table with auto-generated “ID” and a column “Name”. Here it is
ID Name
0 John
1 Julian
2 Carol
3 Anthony
4 Paul
5 Chris
6 Bob
I then create a new table “Sorted”. This just has a single column “Name”. I create it in design view and when I save it I do not create a Primary Key.
Then using Tools > SQL… I run these commands
Insert into “Sorted” select “Name” from “Data” order by “Name” Asc;
Alter Table “Sorted” add column “Item” INTEGER GENERATED BY DEFAULT AS IDENTITY(START WITH 1) before “Name”
I get this
Item Name
1 Anthony
2 Bob
3 Carol
4 Chris
5 John
6 Julian
7 Paul
which is what I want.
If I add more rows to the “Data” table, and want to recreate the “Sorted” table, I need to run these SQL commands
Delete from “Sorted”;
Alter Table “Sorted” drop column “Item”;
Insert into “Sorted” select “Name” from “Data” order by “Name” Asc;
Alter Table “Sorted” add column “Item” INTEGER GENERATED BY DEFAULT AS IDENTITY(START WITH 1) before “Name”