splitting a database into separate tables

[jon@thebear]

I have imported a database into Access 2003, and the data is all in one large
table.
I now need to split the data into about 12 tables, before designing how the
tables relate to each other. How do I split the database into separate
tables, preferably without losing the relationships which exist in the
current composite database?

 

[Douglas J. Steele]

Create the new tables, then write Append Queries to take the individual
values from the existing table and put them in the new tables.

 

[Peter Hibbs]

You need to do it the other way round. First work out which fields
belong in which tables and export them to an Excel or a .csv file.

Then use the Excel to Access Converter Utility program at :-
http://www.rogersaccesslibrary.com/...me='Excel to Access Converter Utility program'
to copy the data into the separate tables. The program will link the
tables for you automatically.

Peter Hibbs.

 

[Tony Toews [MVP]]

I have imported a database into Access 2003, and the data is all in one large
table.
I now need to split the data into about 12 tables, before designing how the
tables relate to each other. How do I split the database into separate
tables, preferably without losing the relationships which exist in the
current composite database?

I'm with Peter. Figure out what data needs to go into what 12 or so
tables first. Then split the data into those 12 tables.

Take your time. And maybe pay someone who has done a lot of work with
relational databases to review things once you've setup the new
tables.

Tony
--
Tony Toews, Microsoft Access MVP
Please respond only in the newsgroups so that others can
read the entire thread of messages.
Microsoft Access Links, Hints, Tips & Accounting Systems at
http://www.granite.ab.ca/accsmstr.htm
Tony's Microsoft Access Blog - http://msmvps.com/blogs/access/

 

[jon@thebear]

Thanks Doug, Peter and Tony. Will do what you suggest.

Jon

 

[jon@thebear]

Peter, I have tried to download the converter utility below, but get the
error message "ADODB.Recordset error '800a0bb9'

Arguments are of the wrong type, are out of acceptable range, or are in
conflict with one another.

/Otherdownload.asp, line 32"

What do I need to do to get this converter?

 

[Peter Hibbs]

Try http://www.rogersaccesslibrary.com/index.htm and click on Other
Developers Libraries, you should find it there under my name.

Peter Hibbs.

 

[jon@thebear]

Hi Peter, thanks for your message. I followed your links, double-clicked the
Download A2003 button, and clicked "open" on the file download screen.
Nothing happened. What am I doing wrong? Jon

 

[Peter Hibbs]

Hi Jon,

No idea, it's not my Web site. I suggest you try it again (maybe a
temporary glitch) and if it still doesn't work then email me at
(e-mail address removed)_SPAM (omit the NO_SPAM bit) and I will
send you the program direct.

Peter Hibbs.