Splitting one table into two - easiest way?

[Kevin_S]

Alright, I haven't been using access that much over the last few years
and I need to hopefully find a fairly easy way to split a table into
two seperate tables. Trying to help out a local non-profit
organization.

This started as a old Paradox database which I converted to Access.
This data was from years ago, but is now going to be needed. The
biggest problem is the database is not normalized. They have one
table, and it should be at least two.

Basic Sample of fields:
pledgeID, fname, lname, address, phone, pledge desc, additional needed
fields regarding each pledge, etc.....

The problem is, some people have pledged 10 or more times... so we have
10 different records with their name and address there was a lot of
problems with data entry. I already had them go through it and fix all
the errors we could find (we needed to do a mailing from the database)
but I think we need to normalize this database for the future work I
will do on it (forms, reports, etc).

The table contains 1100 records. I figured the best way going forward
was to create a seperate table with the name, address, phone data, and
just create a key to be used back in the pledge table. But with 1100
records I need a fairly easy way to do it. We need to track the
individual pledges and keep data on it, but the multiple entries of
personal data is not good. They might as well have used a spreadsheet!

I'm assuming I need some combination of queries and creating an
autonumber or other key field. Help me out, I'm not used to going back
and fixing others work, the databases I create are normalized from the
start. It's been a few years since I've done a lot in access and I'm
just kinda drawing a blank on how to do this efficiently.

 

[Kevin_S]

I probably should have mentioned I'm working in Access 2000, but if
needed I can do this on-site using 2003.

 

[Phil Hunt]

Try Toos/Analyse/Table from the menu. Maybe it's your lucky day.

 

[Kevin_S]

Thanks... I'm trying that right now, it looks promising but has pulled
up 700 records in the "correcting typographical errors" part. I'm
trying to figure out how to use the "correction" field right.

Why so many? Is it picking up stuff like similar zip codes? Or name
fields being blank? We have a fnme, lname, and company name field. So
some records are by company name, some are by individual names.

 

[Roger Carlson]

I've actually had pretty good luck with the Table Analyzer Wizard. It will
split your tables (actually it creates two new tables, leaving your existing
table alone) and creates the relationship between them. Then it will split
the data and fill the foreign key value in each child record.

Things to look out for:
1) The first couple of screens just give examples of non-normalized tables
and don't have anything to do with your database. Ignore them if you
already understand normalization.
2) NEVER allow the wizard to decide which fields to put where. Do it
yourself.
3) I found the interface counter-intuitive. It often did things backwards
to how I thought it should.
4) It insists on creating a Look-Up field for the foreign key field in the
child table, so I would always have to go in and change it back to textbox.
(I NEVER use Lookup fields.)
5) It sometimes fails on very large tables (ie tables with a lot of
records), but I don't think you'll have a problem with 1100 records.

The upside (as I mentioned before) is that it doesn't affect your existing
tables, but creates new ones, so if you mess something up, you can just
delete them and try again. Give it a try.

NOTE: ALWAYS back-up your database before you do anything like this. I've
never had it corrupt a database, but you never know. A backup is simple and
easy insurance.

--
--Roger Carlson
Access Database Samples: www.rogersaccesslibrary.com
Want answers to your Access questions in your Email?
Free subscription:
http://peach.ease.lsoft.com/scripts/wa.exe?SUBED1=ACCESS-L

 

[Kevin_S]

I think that might have done it! I still have a some duplicate
records, but I should be able to go through and clean it up in both
tables pretty easy now. HUGE thanks!

I accidentally left one field in the old table, can I still move that
over to the other or do I need to repeat the process from scratch?

 

[Kevin_S]

Thanks.. most of your comments were spot on...

I didn't let the wizard decide, and I also ended up removing the
lookups just like you said you did

 

[Phil Hunt]

I don't know. I never used it, always did it right from the get go.
THe splitter could go over borad at time. You should just let it split at
where it should split, ignring the rest.