Need HELP / Increment ID (number) for each unique text string

[tHeRoBeRtMiTcHeLL]

Below is an earlier post to an Excel Group..
....but I thought that there might be a way to do this in Access
by importing data and then creating append and/or update query. I would
most certainly need to use the right type of table join and criteria in
the query to perform the task, and don't find myself an expert or up to
par as far as I'm concerned.

*************** *************** *************** *************** *******
Hello everyone,
I've run into a wall. I have a 4 column list of data.
Column 1 has a list of US Cities.
Column 2 has a list of Unique IDs for each State in Column 3.
Column 3 has a list of US States.
Column 4 has a list of US Zip Codes for the above entities.
Ex.
CITY STATE_ID STATE ZIPCODE
Akiachak 1 Alaska 99551
Akiak 1 Alaska 99552
Akutan 1 Alaska 99553
Abbeville 2 Alabama 36310
Adamsville 2 Alabama 35005
Addison 2 Alabama 35540
(Etc. With a total of 28,758 rows of the same.)
The following is an example of what I need done.. and I have decided
that manually doing this would be a rather mundane PITA.
CITY_ID CITY STATE_ID STATE ZIPCODE
1 Akiachak 1 Alaska 99551
2 Akiak 1 Alaska 99552
3 Akutan 1 Alaska 99553
4 Anchorage 1 Alaska 99508
4 Anchorage 1 Alaska 99515
4 Anchorage 1 Alaska 99518
5 Abbeville 2 Alabama 36310
6 Adamsville 2 Alabama 35005
7 Addison 2 Alabama 35540
8 Anniston 2 Alabama 36205
8 Anniston 2 Alabama 36206
8 Anniston 2 Alabama 36201
My question is this, is there an easier way to do this...
programmaticall y or via formula? If so, can someone please
give me a hint in the right direction?
I'm guessing it would need to begin with Row 2 and first lookup Column
3 (StateID) then begin to read Column 2 (City - text string)If it is
found to be UNIQUE when comparing to the previous row's text string AND

(very important - StateID remains the same value) THEN Column 1
(CityID) should be assigned a value beginning with 1.
If it is found to be a duplicate string then it should retain the
(CityID) value of the previous row. It then repeats until it finds no
data (NULL). I'm guessing that if this is done with code it will
contain a FOR, IF, NEXT statement and maybe a DO WHILE or two.
Thanks,
Robert

[jleckrone]

Probably your best bet would be to import your spreadsheet into Access
and then create a new tables to store the results in the way you are
looking for. I would suggest making a States table and storing the
StateID and State in that table. Then make another table with the
CityID of type AutoNumber, City, StateId. Then make a third table with
CityId and ZipCode.

You now need three queries. Create a new query in design view and
select the table that you imported to Access. Right click in the gray
area and select Properties. On the row labeled Unique Values, put Yes
and close the Properties window. Now Double-click on StateId and
State. They should appear in two columns below. In the menu bar,
click Query, then Append Query. You want to Append To your States
Table. Run the query by clicking Query, Run in the menu bar, or the
icon with the red exclamation point in the tool bar.

Make another append query from your imported table only this time
append the City and the StateID to your Cities table.

Make a third Append Query from the imported table. This time you are
going to select the imported table and the Cities table. You want to
link the City Names from each table together and the StateIDs from each
table together. You link them by selecting one and dragging it over to
the other. Now append CityId and ZipCode to the ZipCode Table.

To view the data in the format you are wanting, make a query that links
the Cities, States, and ZipCodes tables.

Hope that helps you!

CITY_ID CITY STATE_ID STATE ZIPCODE
1 Akiachak 1 Alaska 99551
2 Akiak 1 Alaska 99552
3 Akutan 1 Alaska 99553
4 Anchorage 1 Alaska 99508
4 Anchorage 1 Alaska 99515
4 Anchorage 1 Alaska 99518
5 Abbeville 2 Alabama 36310
6 Adamsville 2 Alabama 35005
7 Addison 2 Alabama 35540
8 Anniston 2 Alabama 36205
8 Anniston 2 Alabama 36206
8 Anniston 2 Alabama 36201
My question is this, is there an easier way to do this...
programmaticall y or via formula? If so, can someone please
give me a hint in the right direction?
I'm guessing it would need to begin with Row 2 and first lookup Column
3 (StateID) then begin to read Column 2 (City - text string)If it is
found to be UNIQUE when comparing to the previous row's text string AND

(very important - StateID remains the same value) THEN Column 1
(CityID) should be assigned a value beginning with 1.
If it is found to be a duplicate string then it should retain the
(CityID) value of the previous row. It then repeats until it finds no
data (NULL). I'm guessing that if this is done with code it will
contain a FOR, IF, NEXT statement and maybe a DO WHILE or two.
Thanks,
Robert