Once you have the lookup table done [Table2] and have added the foreign key field, Venue_ID, to Table1, you can ititially populate the Venue_ID field in Table1 with the following:

USE TABLE1
USE TABLE2 in 0 order VenueName
set relation to upper(VenueName) into TABLE2
REPLACE All Venue_ID with TABLE2.ID
SET FILTER TO Venue_ID = 0 && these will be the unmatched names
LOCATE

Both TABLE1 and TABLE2 need to have an index on UPPER(VenueName). After you do the REPLACE, you will have to reconcile those that had no matching names in TABLE2.


>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)