Help - Convert Access flat file to normalized tables

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Steve Newton - 28 Jun 2004 21:16 GMT

Folks,

Hi. I have an Access file that consists of a single flat file table.
I'd like to create a series of tables that are normalized. I would
like to do this manually, rather than using the Tools>Analyze>Tables
approach, which will create look-up fields. My reading indicates that
look-up fields (vs. look-up tables) are the Devil's work. :)

I have read and studied a good tutorial on the subject (the URL is
noted below).

http://pubs.logicalexpressions.com/Pub0009/LPMArticle.asp?ID=182

However, I still can't quite get things right, using the approach
described in the tutorial.

Is there anyone who would be willing to correspond with me via email
to help? If so, I could be more explicit about the problems I've
having. I'm afraid that trying to do this in the context of the
mailing list will try the patience of most subscribers.

TIA,

Steve
jnewton@uoregon.edu

Reply to this Message

Kevin3NF - 28 Jun 2004 21:37 GMT

Steve,

Most people here will be happy to answer any specific question you have, and
most of the broad ones. Getting private one-on-one help may turn into a
paid arrangement.

Generally speaking, take a look at the information you have, and break it
into logical entities. That is your starting point for your new table
design.

Signature

Kevin Hill
President
3NF Consulting

www.3nf-inc.com/NewsGroups.htm
www.DallasDBAs.com/forum - new DB forum for Dallas/Ft. Worth area DBAs.

> Folks,
>
[quoted text clipped - 21 lines]
> Steve
> jnewton@uoregon.edu

Reply to this Message

John Vinson - 28 Jun 2004 23:21 GMT

>Folks,
>
[quoted text clipped - 3 lines]
>approach, which will create look-up fields. My reading indicates that
>look-up fields (vs. look-up tables) are the Devil's work. :)

That they are... <g>

>I have read and studied a good tutorial on the subject (the URL is
>noted below).
[quoted text clipped - 8 lines]
>having. I'm afraid that trying to do this in the context of the
>mailing list will try the patience of most subscribers.

Well, without going to the tutorial, I'll just toss out the approach I
use when this operation is needed.

- Use logic (aided by a #2 pencil, a block eraser, and a pad of paper;
or a whiteboard with a good eraser) to design a properly normalized
set of tables, based on the real-world logical structure of the data.
Reference to the existing table at this point is *only* for the
purpose of identifying what information is needed.

- Create empty tables with the appropriate linking fields and
relationships.

- Run as many Append queries as necessary to migrate the data from the
wide-flat table into the normalized tables. These queries will make
free use of the UNIQUE VALUES query property to extract only one
instance of sets of duplicated fields, and may involve joining the
first-created table to the wide-flat table in order to pick up the
value of a new primary key field for use in a foreign key.

By all means post back with more details. As Kevin says, most of the
volunteers here would prefer to keep volunteer work to the newsgroup;
private EMail support is usually available to paying customers.

John W. Vinson[MVP]
Come for live chats every Tuesday and Thursday
http://go.compuserve.com/msdevapps?loc=us&access=public

Reply to this Message

Steve Newton - 29 Jun 2004 00:54 GMT

>- Use logic (aided by a #2 pencil, a block eraser, and a pad of paper;
>or a whiteboard with a good eraser) to design a properly normalized
[quoted text clipped - 4 lines]
>- Create empty tables with the appropriate linking fields and
>relationships.

John and Kevin,

Thanks. I have created the design for the normalized tables on paper.
So far, so good. I have also created the empty tables with the
appropriate linking fields and relationships.

>- Run as many Append queries as necessary to migrate the data from the
>wide-flat table into the normalized tables. These queries will make
>free use of the UNIQUE VALUES query property to extract only one
>instance of sets of duplicated fields, and may involve joining the
>first-created table to the wide-flat table in order to pick up the
>value of a new primary key field for use in a foreign key.

In the tutorial, I was advised to begin creating my tables with
make-table queries (rather than beginning with empty tables). I
suspect that some of my problems began here. In any event, my two
questions at this point are:

1. When I created the empty tables with appropriate linking fields and
relationships, should I have enforced referential integrity, or just
established the relationships without enforcing referential integrity?

2. I know that I need to get from my wide flat-file table to the
tables with the relationships. I understand the unique-values
property, which was explained well in the tutorial I read, Can you,
however, explain a little more about the phrase "...and may involve
joining the first-created table to the wide-flat table in order to
pick up the value of a new primary key field for use in a foreign
key"? I think this is where I am messing up somehow. When I created
the empty tables, I established the PKs and FKs. In doing this, should
I have somehow accounted for specific fields in the wide flat-file
table?

Many thanks,

Steve

Reply to this Message

John Vinson - 29 Jun 2004 01:48 GMT

>In the tutorial, I was advised to begin creating my tables with
>make-table queries (rather than beginning with empty tables). I
>suspect that some of my problems began here. In any event, my two
>questions at this point are:

That can sometimes work, but IME you have more control over field
types and field sizes if you create empty tables using the user
interface. Either way will work.

>1. When I created the empty tables with appropriate linking fields and
>relationships, should I have enforced referential integrity, or just
>established the relationships without enforcing referential integrity?

A relationship without referential integrity is just a suggestion; it
provides NO protection against entering invalid data. By all means
establish RI.

>2. I know that I need to get from my wide flat-file table to the
>tables with the relationships. I understand the unique-values
[quoted text clipped - 6 lines]
>I have somehow accounted for specific fields in the wide flat-file
>table?

Not knowing anything about your data I can only speak in general
terms; but let's consider an example. Suppose you had a wide-flat
sales database with fields CustomerName, SaleDate, Item, Quantity,
Cost; and you want to create three tables, Customers, Items, and
Sales.

You could create a Customers table with an autonumber ID, and run a
Unique Values query selecting CustomerName and appending into this
table, getting one record for each customer (or, more precisely, one
record for each spelling variant of each customer). Do the same for
Items.

The Sales table would have foreign key fields CustomerID (long
integer, linking to the Customers table autonumber ID) and ItemID
(linked to Items autonumber ItemID) - but your wide-flat table doesn't
have these fields! So you would create a Query joining WideFlat to
Customers *by customer name*, to pick up the ID, and likewise joining
it to Items *by Item* to pick up *that* ID.

John W. Vinson[MVP]
Come for live chats every Tuesday and Thursday
http://go.compuserve.com/msdevapps?loc=us&access=public

Reply to this Message

Steve Newton - 29 Jun 2004 16:02 GMT

>The Sales table would have foreign key fields CustomerID (long
>integer, linking to the Customers table autonumber ID) and ItemID
>(linked to Items autonumber ItemID) - but your wide-flat table doesn't
>have these fields! So you would create a Query joining WideFlat to
>Customers *by customer name*, to pick up the ID, and likewise joining
>it to Items *by Item* to pick up *that* ID.

John,

Many thanks! After reading the above paragraph a couple of times, and
drawing its implications on a note pad, I managed to turn my single
wide-flat table into 4 normalized tables that do exactly what I want.

This will result in my being able to develop a form and subforms that
make my successor's job of entering data much, much easier. And, of
course, it will aid my subsequent analysis of the data.

This newsgroup has been invaluable to my interest in learning the
concepts that underlie the practical application of Access.

Thanks to Fred and Kevin too, not to mention the hundreds of others
who freely give their advice and encouragement each day.

Steve

Reply to this Message

John Vinson - 29 Jun 2004 20:27 GMT

>Many thanks! After reading the above paragraph a couple of times, and
>drawing its implications on a note pad, I managed to turn my single
[quoted text clipped - 3 lines]
>make my successor's job of entering data much, much easier. And, of
>course, it will aid my subsequent analysis of the data.

Glad to have been of assistance! And thanks for the feedback: it's
good to know that we're having a beneficial effect.

John W. Vinson[MVP]
Come for live chats every Tuesday and Thursday
http://go.compuserve.com/msdevapps?loc=us&access=public

Reply to this Message

Fred Boer - 29 Jun 2004 14:47 GMT

You probably don't need this advice, but make sure you have backups! Also,
you might consider taking the time to make some notes as you work your way
through the process, or even save a new backup after every major step in the
process. I did this kind of thing once, and found myself, after discovering
I'd made some mistake or other along the way, having to redo the *whole*
process from scratch. And I had forgotten some of the details along the way.
Eventually, I learned to make notes about just what I was doing at each
step, and I saved a copy of the database after each step, so that if I
discovered a mistake, I just had to go back one or two backups, rather than
start over.

Naturally, if your database is huge this might not be feasible...

HTH
Fred Boer

P.S. I'll leave the experts to help you with the actual work! ;)

> >- Use logic (aided by a #2 pencil, a block eraser, and a pad of paper;
> >or a whiteboard with a good eraser) to design a properly normalized
[quoted text clipped - 41 lines]
>
> Steve

Reply to this Message

Fred Boer - 29 Jun 2004 14:52 GMT

...Although you will undoubtedly make fewer mistakes than me and may not
make any at all! <g>

Fred

Reply to this Message