Split one table into 2 (relational) ones (1 Viewer)

[thewilli]

Hi there!

I'm currently working on a malformed Access database, not normalized at all.

It only consists of one table and is designed like this:

(table compartment)

#ID#Name#Member1#Member2#Member3#Member4#...#Member20#

So all the members are realized by single columns.
I created a new database, consisting of the main table, the "people" table and a relation table, connecting the people with the compartment (n:m relation).

It works great but I have to get the old data into the new tables. The old main table consists of more then 100 columns, and most of them are now hived off by using relations.

Now I'm looking for a smart solution to convert the table.

Do you have any idea? I tried to use a Query, but I couldn't merge two or more columns and put them in a single one on a new table.

Thank you in advance!

 

[Uncle Gizmo]

I have been working on a form to do this, I really want to make it into a generic form, suitable for any database but I haven't quite got that far with it yet. But I would like a guinea pig to try my first attempt on if you're interested.

 

[Uncle Gizmo]

btw I won't be able to do much tomorrow Wednesday 6th But I should be able to have a look on Thursday 7th

 

[neileg]

I would run 20 append queries, one for each member number.

 

[thewilli]

I have been working on a form to do this, I really want to make it into a generic form, suitable for any database but I haven't quite got that far with it yet. But I would like a guinea pig to try my first attempt on if you're interested.

I really look forward to try this!

I would run 20 append queries, one for each member number.

The real table (the stuff above was just an example how the table looks like) consists of 168 columns and I definitely will not create hundreds of append queries

 

[neileg]

The real table (the stuff above was just an example how the table looks like) consists of 168 columns and I definitely will not create hundreds of append queries

So you're going to retype all of the data?

Sometimes it's easier to hack data about in Excel, but you'll still have to do 168 copy and pastes.

 

[thewilli]

the main problem with that is that the old database is still in use. On a certain date I'll have about 30min to convert the data - so I really need a template. I can't copy all that stuff in that time

 

[neileg]

Then I think you need 168 append queries!

 

[ed_the_unlucky]

thewilli: if it's structured member1, member 2, etc., i would think that you could write a procedure to run the same append query the requisite # of times, no? am i missing something?

but 168 fields?! don't tell me it duplicates [fields] for each of the 1-20?!

ed

 

[Uncle Gizmo]

Please could you post a Zip copy of your database So I can have a look.

 

[Uncle Gizmo]

Transpose From Spreadsheet Type To DB Type

Here is my 1st Attempt, Please try it and provide feedback. (Removed See next message for details )

 

[Uncle Gizmo]

I have updated the previously posted form and started a new thread based on it here:

I will now delete the old form to avoid the propagation of redundant forms.

 

[stopher]

So you're going to retype all of the data?

Sometimes it's easier to hack data about in Excel, but you'll still have to do 168 copy and pastes.

I agree. You're probably going to only take about ½hr of mind numbing concentration (hopefully no mistakes).

I might even go so far as to copy and paste some append SQL 168 times and just change the text by hand for a more robust approach.

But I took a look at Uncle Gizmo's tool. Very nice. I'm looking forward to hearing how Willi gets on with it. Of course he may spend more time installing and getting it to work. But what database freek really wants to spend their time copying and pasting and running the risk of errors even if it is quicker

Chris

 

[thewilli]

Sorry for the delay - I got ill but will now try your tool asap!