I have a table that keeps track of venues (bars, theaters, etc.) with two fields in it:



Table1

ID# (points to records where an act played at the venue )

VenueName (text field that is the name of the venue)



In this table, there are many multiples of the same venue, of course. I have cleaned this list so that as many names are identical as possible.



What I want to do is this:



(1) Create a Table2 with the above ID# (unchanged) and the VenueName concatenated so that only one unique occurrence of any venue name is in the list. I would then add a 3rd field (NewID#) to this table, numbering the unique occurrences with a unique number, from 1 to whatever.

(2) I would add a blank field to the original Table1 and name it NewID# to match Table2.

(3) Then I need to set up a relation so that I can copy the NewID# from Table2 into every occurrence of the VenueName in Table1.



I don't need this done, but to be shown how to do it, since I have many different tables like this to deal with.



Anyway, that is an example of what I need to do, in this case. Let me know your thoughts.



Thanks,



Michael Erlewine (michael@erlewine.net)