Utility to split, normalize, and relate a table?

[spinjector]

Hi, I am looking for some code to "divide and conquer" duplicate entries in my databases. This is just for importing and normalizing existing data, with the intention of designing a database around it. I have started my own already, but I'd like to pick over what someone else may have done, to see if there is a different/better way to do it.

I would like to do the following:

1) Look at a column in a table that needs to be normalized.

2) Run a duplicates query on that column.

3) Present the query results to the developer.

4) Allow the developer to see, cleanup, and combine similar entries.

5) Create a new table from the cleaned up data.

5) Create a new column in the primary table, formatted for a Long Integer.

6) Create a relationship between the ID field in the new table, and Long Integer field in the primary table.

7) Delete the old field from the primary table.

Thanks.

 

[maxhugen]

I'd do this in code.

1. Import data to a temp table.
2. Create your 'primary' and 'child' tables.
3. Create query that sorts by 'primary key' field, then the duplicated second field.
4. In code, loop through a recordset based on this query. Each time the 'primary' field changes from one row to the next, run an Insert query to add a record to your Primary table.
5. For every row of the recordset, run an Insert query to add the record to your 'child' table.

HTH

Max Hugen
Australia

 

[spinjector]

Yea I was working on the code today. I wanted to do it in a query just to learn how to do it. Code is too easy.