URGENT - Converting single table to relational design

[Dave_FFM]

Hi

The company i work for have previously used a single table database with
50-60 coloumns and 23000 records, so i have got in touch with a developer who
created a model for the relational design. I asked him to transfer the old
information (in the massive table) across to the new design which he tried
but has told me this morning that in order to have every record linked it
would need to be done manually!! Thats 10 tables with 23000 records, and if
you do the maths it's no easy task.
Can anyone help and possibly advise me on any script that can be written or
anything at all i can do without having to start over again?

Thanks in advance

Dave

 

[Stefan Hoffmann]

hi Dave,

The company i work for have previously used a single table database with
50-60 coloumns and 23000 records, so i have got in touch with a developer who
created a model for the relational design. I asked him to transfer the old
information (in the massive table) across to the new design which he tried
but has told me this morning that in order to have every record linked it
would need to be done manually!! Thats 10 tables with 23000 records, and if
you do the maths it's no easy task.
Can anyone help and possibly advise me on any script that can be written or
anything at all i can do without having to start over again?

Doing it manually means:

you need to copy the data for each table in the correct order using
queries. Maybe you need to do some coding, as in some cases you need to
loop through your existing data.

Manully does not mean: Reenter the data.

mfG
--> stefan <--

 

[BruceM]

Without some details of what is in the old table and how the information is
to be related in the new design, there is no way to offer anything but the
most general suggestions. I'm not sure what the consultant meant about
needing to link the records manually. I suppose he could have meant there
is no built-in linking field, but that is just speculation.

 

[Dave_FFM]

Without some details of what is in the old table and how the information is
to be related in the new design, there is no way to offer anything but the
most general suggestions. I'm not sure what the consultant meant about
needing to link the records manually. I suppose he could have meant there
is no built-in linking field, but that is just speculation.

It is quite difficult to explain how the information relates as it is quite
complex but hours were spent explaining how they do so the actual design is
no problem, but the 50 odd coloumn were to be split into 10 tables of 5
coloumns and linked via a unique identifier. By manually he meant that if
they were to be queried and copied across then each record must be given a
relation to the relevant information on the other tables one record at a time
rather then pre-set relationships working it out for all.

Cheers

 

[Dave_FFM]

Thanks Stefan,
Please see my reply to BruceM for more detail.

cheers

 

[Stefan Hoffmann]

hi Dave,

It is quite difficult to explain how the information relates as it is quite
complex but hours were spent explaining how they do so the actual design is
no problem, but the 50 odd coloumn were to be split into 10 tables of 5
coloumns and linked via a unique identifier. By manually he meant that if
they were to be queried and copied across then each record must be given a
relation to the relevant information on the other tables one record at a time
rather then pre-set relationships working it out for all.

When your original data contains candidate keys then you can do it with
queries and mapping tables (current row -> id in new tabls(s)).

If there are no candidate keys, thus you cannot use a select to identify
your rows, you need VBA and a recordset to loop through your table.


mfG
--> stefan <--