Normalizing data

Hi,

Say you have a field (lets call it Name) within a table - this Name field contains 100,000 entries which broken down is 5000 different names which are repeated several times.

To create a new table with a Name ID field and just list the 5000 without having to keep deleting the duplicates but making sure you have them all, how do you go about this?

Thanks

Chris

Assumptions:
You have built the new table and defined the fields.
The Name field has a unique index on it.
The ID field is an autonumber field


Use a query like this to append the records to the table.

INSERT INTO NewTable(UniqueNameField)
SELECT DISTINCT NameField
FROM TheOldTable
WHERE NameField Is Not Null

If you are using the query grid, then build the query with just the fields
you want to transfer, set the query's Unique VALUES property to Yes and then
select Query: Append Query from the menu. You'll be prompted for the name
of the table you want to add records to and an Append To: row will appear
for you to specify the destination fields.

"bagnallc" wrote in message
...

Hi,

Say you have a field (lets call it Name) within a table - this Name
field contains 100,000 entries which broken down is 5000 different
names which are repeated several times.

To create a new table with a Name ID field and just list the 5000
without having to keep deleting the duplicates but making sure you have
them all, how do you go about this?

Thanks

Chris


--
bagnallc