Access Database Normalisation

From existing flat DB

[ukgimp]

I have managed to extract a large database (40k records) and need to normalise it. Some fields are fine and others need to be reassigned a number and referenced in another table.

I have a couple of columns where multiple entries are separated by a “/” or “:”. I can strip out the dupes, no problem. Any ideas how I can loop through the database and get each delimited value, look for it in the respective column find their ids and insert them into a many to many table.

Does that make sense. If yes, any pointers. I don’t have time to go the 40K route by hand :)

Cheers

I struggled with choosing the right forum, feel free to move if required

[aspdaddy]

No easy way to do this. You need to add the lookup table (minimum fields ID & Value) and add a extra table to hold the Lookup ID's and MaterRecord ID's - composite key/join table.

Then run a procedure something like this -

- SELECT Master_ID, delimField FROM MasterTable
- Loop the Recordset
- Split the delim fields into an array
- Loop thru the array lbound-ubound (i)
- SELECT Lookup_id FROM Lookup WHERE value= array(i)
If eof Then
INSERT Value in Lookup,
SELECT its ID From Lookup
End If
INSERT LookupID,MasterID into NEW table
- Next
- Next

Alternatively see if theres a tool that does this kind of thing - possibly Scribe Migrate, I have used it for similar but less complex jobs.