Change From One Large to several relational tables

[Guest]

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?

 

[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.

 

[Joseph Meehan]

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?

You all ready have an opinion different from mine and I will guess you
will get more.

I believe the wizard does a good job, but it is far from perfect. It
does as well or better than I could without a good knowledge of the data
current and future. Since splitting is part science (splitter does a good
job there) and science, (splitter does poor there) you really need to help
out the wizard.

I suggest you make a copy of your database and use the wizard on it.
Take a good look at what it did. Try to figure out why it did what it did.
That should teach you a lot. Now that you know what it did, try to think of
better ways.

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

Not really. Most of the forms and reports can stay the same. You may
need to create on or more queries to feed them. You can create custom
queries for each one or group of reports and forms or you may just want to
make one query that will put everything back together as if it were in one
table. What is best depends on the specifics of your project. Micros may
need to be tweaked.

3. Any other ideas?

Sure, you are doing well. After a few more projects, this will all come
easy.

 

[Guest]

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.

 

[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?

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]