Converting Flat Table to Relational Structure

[sophie_ross]

Does anyone know the procedure MSA uses to order records when assigning the autonumber field?

Oh good lord, where to start. I've merged 6 separate non-relational (poorly designed) access databases into one, got rid of duplicate records and built a new structure for the data using a relational database with 11 tables. My problem is that I need to populate my new structure with the data from the old table and it just ain't happening!

The problem lies with the primary key - I want to retain the original primary key so I can keep track of the records while I sort out what data goes into which table. BUT then I can't switch that primary key back to Autonumber of course. So, I created a new field for autonumber but when it assigns the number I can't work out how it indexes the records since the consecutive numbering doesn't match the old primary key! This would be easy to manage if it applied the same rule to each table I update so the records all retained the same key even though it didn't match the original but it doesn't!

Basically how do I split up these 2500 records into a dozen tables whilst retaining their primary keys so the records still relate to each other but new keys will be assigned after...

Does that make any sense?!

 

[FoFa]

Makes perfect sense.
Let me tell you what I have done with this kind task in the past. You decide if it will work for you. First bite the bullet and just redesign your tables to use your autonumber fields as primary/foreign keys. Forget your old indexes (sort of). Account for your indexes (by creating fields to hold them) and then populate your tables. Now run queries to populate the NEW foreign keys (from your autonumber fields) based on the old indexes. You have to have some index to relate back to another table (keep that in mind when you populate). Once all the new keys are populated, you really don't need the old indexes anymore (for this application).

 

[sophie_ross]

That sounds like the direction I thought I should take but how can I use a query to populate the relevant fields? How do I write a query that checks the contents of one field in one table against one field in another and updates a totally separate field based on the result?

 

[FoFa]

TblA NKey OIdx ' (NKey is new autonum, OIdx is old index)
TblB FKey, OIdx ' (FKey is new foreign key pointer to TblA)

UPDATE TblB
SET FKey = (Select NKey from TblA where TblB.OIdx = TblA.OIdx)
WHERE OIdx is not null

This assumes OIdx is unique to TblA

 

[sophie_ross]

Thank you SO much! All sorted!