How to normalizing a Table?

[Guest]

Hi,

I am a novice to Access 2002. I am working on a project that has grown
beyond my learning curve. What I have is a form with a Tab Control (7 tabs).
I originally had planned on putting all the fields from each Tab into one
Table then use the Normalize wizard to slim down the database. However, I
ran into the following problem “The row you inserted in the grid exceeds the
limit of 255 rows (fields) for a tableâ€. I know why this has happen but what
I was hoping to learn is a way to put all the fields that the team is
requesting and still use the normalization wizard. I was also wondering if
it would be a good idea to create a table for each tab and setup
relationships between the tables. I have tried this on a limited basis and
could not get it to work correctly. Any suggestions or pointer would be
greatly appreciated.


Leopold

 

[John Vinson]

Hi,

I am a novice to Access 2002. I am working on a project that has grown
beyond my learning curve. What I have is a form with a Tab Control (7 tabs).
I originally had planned on putting all the fields from each Tab into one
Table then use the Normalize wizard to slim down the database. However, I
ran into the following problem “The row you inserted in the grid exceeds the
limit of 255 rows (fields) for a table”. I know why this has happen but what
I was hoping to learn is a way to put all the fields that the team is
requesting and still use the normalization wizard. I was also wondering if
it would be a good idea to create a table for each tab and setup
relationships between the tables. I have tried this on a limited basis and
could not get it to work correctly. Any suggestions or pointer would be
greatly appreciated.

Stop.

You're going about this process EXACTLY BACKWARDS. What you're doing
is like assembling your house's roof, frame, windows, and doors and
then deciding where you're going to pour the foundations.

Turn off your computer; dig around and find a pencil and a sheet of
paper (if you have such around the house <g>). Identify all of the
types of Entities - real-life things, persons, or events of importance
to your system. Each Entity will need its own table.

For each Entity, identify the significant Attributes of that entity:
atomic chunks of information you need to know about the entity. A
Person entity would have attributes such as LastName, FirstName, and
maybe DateOfBirth, DepartmentNo, etc. - whatever information peculiar
to an individual is needed for your application. If you find yourself
thinking about fields named (say) Category1, Category2 and Category3,
you've discovered a new entity - and a new table. Each valid Attribute
will be a new field in that table.

Then identify the relationships between your tables. Does one
Department have many People? If so you have a one-to-many
relationship. Does one Widget have zero, one or more Categories, and
does each Category have zero, one or more Widgets? If so you have a
many to many relationship and you need another table (related one to
many to Widgets and also related one to many to Categories).

After you've gotten this far, create your Tables (empty); use the
Relationships window to link them together.

NOW you can start thinking about Forms (and not the plural; a single
Grand Master Form which does everything is *not* a good idea).

John W. Vinson[MVP]
Join the online Access Chats
Tuesday 11am EDT - Thursday 3:30pm EDT
http://community.compuserve.com/msdevapps