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

thewilli

New member
Local time
Today, 01:47
Joined
Feb 5, 2008
Messages
7
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

Nifty Access Guy
Staff member
Local time
Today, 08:47
Joined
Jul 9, 2003
Messages
16,243
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

Nifty Access Guy
Staff member
Local time
Today, 08:47
Joined
Jul 9, 2003
Messages
16,243
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

AWF VIP
Local time
Today, 08:47
Joined
Dec 4, 2002
Messages
5,975
I would run 20 append queries, one for each member number.
 

thewilli

New member
Local time
Today, 01:47
Joined
Feb 5, 2008
Messages
7
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

AWF VIP
Local time
Today, 08:47
Joined
Dec 4, 2002
Messages
5,975
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?:rolleyes:

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

thewilli

New member
Local time
Today, 01:47
Joined
Feb 5, 2008
Messages
7
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

AWF VIP
Local time
Today, 08:47
Joined
Dec 4, 2002
Messages
5,975
Then I think you need 168 append queries!
 

ed_the_unlucky

Registered User.
Local time
Today, 04:47
Joined
Jan 28, 2008
Messages
30
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

Nifty Access Guy
Staff member
Local time
Today, 08:47
Joined
Jul 9, 2003
Messages
16,243
Please could you post a Zip copy of your database So I can have a look.
 

Uncle Gizmo

Nifty Access Guy
Staff member
Local time
Today, 08:47
Joined
Jul 9, 2003
Messages
16,243
Transpose From Spreadsheet Type To DB Type

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

stopher

AWF VIP
Local time
Today, 08:47
Joined
Feb 1, 2006
Messages
2,396
So you're going to retype all of the data?:rolleyes:

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. :cool: 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 :D

Chris
 

thewilli

New member
Local time
Today, 01:47
Joined
Feb 5, 2008
Messages
7
Sorry for the delay - I got ill but will now try your tool asap!
 

Users who are viewing this thread

Top Bottom