Change From One Large to several relational tables

J

Jeff C

Having learned the small amount I know by starting from scratch with someone
else's excel spreadsheet...I now have a table with 53 fields.
1. Does the wizard do a good job of splitting into relational tables?
and if so or not, what should I look out for? Is there a better way to do it?

2. I am guessing that i will have to redo all my forms/reports and macros I
have built so far?

3. Any other ideas?
 
B

Brendan Reynolds

1. No, the table analyzer is completely useless.

2. Not necessarily. You may be able to create queries joining your new
tables and base the forms and reports on the queries.

3. The Access 2000 Developer's Handbook goes into a fair bit of detail on
this subject. (I don't have the 2002 edition but I'd be surprised if that
chapter wasn't still in that edition too).

--
Brendan Reynolds (MVP)
http://brenreyn.blogspot.com

The spammers and script-kiddies have succeeded in making it impossible for
me to use a real e-mail address in public newsgroups. E-mail replies to
this post will be deleted without being read. Any e-mail claiming to be
from brenreyn at indigo dot ie that is not digitally signed by me with a
GlobalSign digital certificate is a forgery and should be deleted without
being read. Follow-up questions should in general be posted to the
newsgroup, but if you have a good reason to send me e-mail, you'll find
a useable e-mail address at the URL above.
 
J

Jeff C

I hope that as Joe comments..there will be more responses with different
ideas..that is the treasure of this discussion .... as I have found out.
 
J

John Vinson

Having learned the small amount I know by starting from scratch with someone
else's excel spreadsheet...I now have a table with 53 fields.
1. Does the wizard do a good job of splitting into relational tables?
and if so or not, what should I look out for? Is there a better way to do it?

2. I am guessing that i will have to redo all my forms/reports and macros I
have built so far?

3. Any other ideas?
Click to expand...

1. I'm sort of between Brendan and Joseph on the question of the
wizard. It does a decent job in some cases, and I've seen it do some
real howlers. The logic of relational design requires an understanding
of the real-world entities being modeled by the data, which is beyond
the capabilities of even the best artificial intelligence software at
present! You might use it as a starting point, as suggested, but
there's no substitute for turning off the computer, sitting down with
pencil and paper, and THINKING through the questions "What are the
important Entities?", "How are they related?", and "What are their
relevant Attributes?".

2. I'd strongly suggest doing so. Some of the Reports may in fact work
if you can construct a query joining all the tables to reconstruct
your wide-flat table, but Forms will almost surely fail here; you'll
want instead to go to Forms for the "one" side tables, with Subforms
for the "many".

3. Learn about "Normalizing Union Queries" to migrate the data from
your wide-flat into tall-skinny tables.

John W. Vinson[MVP]
 
Top