Flat vs. relational....table analyzer

[Rod Smothers]

Flat file use for inventory needs to be divided up into about a dozen tables.
Table has about 3300 records. Each record has about 40 fields. One field
is an ACCESS NUMBER which is a unique identifier for all records.

I've looked at table analyzer but I THINK I need an even more granular
approach....plus I don't know how to deal with the lookup fields it creates.
I know those are bad.

What are the ramifications if I copy the table a dozen times and remove the
unnecessary fields and records for each one?

For example, among the information in the existing table is "building",
"room", and "staff"....all of which relate to the LOCATION of inventory.
Using copy method described above, can I delete all fields but these three
AND THE ACCESS NUMBER AND END UP WITH A NORMALIZED "LOCATION" TABLE?

Can the access number be used as the primary key in each of my dozen tables?

Thanks to all who read these posts and respond....this discussion group is
very helpful to those of us just starting with Access.

 

[[MVP] S.Clark]

Normalizing the data can be done with Make Table and/or Append Queries.
This eliminates the need for copying and deleting.

 

[Rod Smothers]

Ok, I used the make table query and that will be faster.
Two questions:
(1)Using this method should I then use my unique "access number" in each
make table query? (Explaination: When I created the "location" table, all
3300 records were created....I guess because of the unique "access number"?
What is the relationship between my "access number" and the need for a
primary key?

(2) In a perfect world, what should a Location table look like for a small
school district with only four schools. My guess is that the "building"
field would have ONLY: school 1, school 2, school 3, and school 4 in
it....only four entries in that field....is this right?

--
Regards,

Rod Smothers

Normalizing the data can be done with Make Table and/or Append Queries.
This eliminates the need for copying and deleting.

 

[Sprinks]

Rod,

Are you saying that a Location fully defines the building, room, and staff?
If so, break it out into a separate table, and include only a LocationID
field in the main table. This “foreign key†corresponds to the primary key
of the Location table, and gives “Access†to all of its fields by using a
query that links the tables together by their corresponding fields for
printing reports, etc.

For data input, rather than the dreaded "lookup field", use a combo box
bound to the foreign key field. By setting the foreign key ColumnWidth to
0", you can display a more user-meaningful text field when the user makes a
selection.

As an example, let’s say you wish to display the building, room, and staff
on your data input form. A combo box with the following properties would do
the job:

RowSource: SELECT Location.LocationID, Location.Building, Location.Room,
Location.Staff FROM Location ORDER By Location.Building, Location.Room;

ColumnWidths: 0â€;xâ€;yâ€;z†(where x, y, and z are large enough to display the
longest building, room, and staff, respectively)

BoundColumn: 1

ControlSource: YourMainTable.LocationID

If x, y, and z are all non-zero, each of these fields will appear in the
drop-down list.

Since “Building†is the first non-zero-width column, it will display in the
combo box after the user makes his selection.

Since the Bound Column is 1, the value of “LocationID†will be stored in the
field specified in the ControlSource property. Note that these fieldtypes
must correspond, namely, integer, if you use an AutoNumber (recommended) or
integer type for the primary key.

To display the other RowSource fields, use the combo box' Column property.
For each, place a textbox, and set its ControlSource to
[YourComboBox].Column(x), where x is the column index, beginning with 0.

In designing a database, I always start by thinking “things†and
“attributesâ€. Each thing--such as Customer, Order, OrderDetail,
Invoice—needs its own table. The attributes become fields in each. If I
find I have the same field in more than one table, other than a foreign key
corresponding to the primary key in another table, the application is not
normalized, and I will modify it to avoid the redundancy.

This general topic--“Database normalizationâ€â€”is IMO the single most
important topic in database design. A well-normalized application makes
creating queries, totals, reports, forms—straightforward and logical. I
highly recommend you study the topic well before finalizing your design.
There are other aspects that I haven’t mentioned, but they are not
conceptually difficult. See the following references for further
information.

Hope that helps.

Sprinks

http://www.mvps.org/access/tencommandments.htm
ACC: Database Normalization Basics
http://support.microsoft.com/?id=100139
http://support.microsoft.com/?id=209534
http://support.microsoft.com/?id=283878

Database Normalization Tips by Luke Chung
http://www.fmsinc.com/tpapers/genaccess/databasenorm.html

Support WebCast: Database Normalization Basics
http://support.microsoft.com/default.aspx?scid=/servicedesks/webcasts/wc060600/wcblurb060600.asp

Database Normalization:
http://burks.bton.ac.uk/burks/foldoc/35/28.htm

5 Rules of Database Normalization:
http://www.datamodel.org/NormalizationRules.html

"Understanding Relational Database Design" Document Available in Download
Center:
http://support.microsoft.com/?id=283698
http://support.microsoft.com/?id=164172

ACC2000: "Understanding Relational Database Design"
http://support.microsoft.com/?id=234208

Fundamentals of Relational Database Design:
http://support.microsoft.com/?id=129519

Database Deisgn Principles:
http://msdn.microsoft.com/library/en-us/dndbdes/html/ch04DDP.asp