| re: Need HELP / Increment ID (number) for each unique text string
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!
[color=blue]
> 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...
> programmatically 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[/color]
| 
