What you need to do is to 'decompose' the table into a number of tables, each
of which represents one 'entity' and each column of which represents the
attributes specific to that entity. The first thing to do is to sit down
with a pencil and paper and draw out the structure diagrammatically,
representing each table as a box with a list of its columns in it and joning
them with lines to show the relationships between them.
Once you are happy that you have a structure which contains no redundancy,
i.e. each 'fact' is stated only once, then you cab design your tables to fit
the structure. Start with the principle entities, e.g. Customers, Products.
These will be the 'referenced' tables. Each of these should have a primary
key column which uniquely identifies each row. This could be a natural key
such as CompanyName, but that's risky as its possible two companies could
have the same name. An autonumber surrogate key column is probably better
therefore.
Appending the data to a referenced table is a matter of creating an Append
Query which appends sets of columns in rows of the MasterList table to the
empty new table. First you need to decide on a column or set of columns in
the Master List which uniquely identifies each company for instance. If you
are satisfied that ComanyName is unque to each company it could be that
field, or it could be a combination of CompanyName and CompanyAddress for
instance. Whatever you decide on you need to index that column or
combination of columns uniquely (no duplicates) in your new Companies table.
In the jargon this column or combination of columns are known as a 'candidate
key'.
When you run the append query only one instance of each company will be
appended to the new Companies table because of this unique index.
Repeat the above for each referenced table you've identified and created.
Once you have your referenced tables set up and filled you can append data
to the referncing tables. An example here might be an Orders table which
includes a foreign key column CustomerID referencing Customers. To fill this
table you need to creat an append query in which you join the MasterList
table to the Customers table you've already filled. The join is on the
column or combination of columns you indexed uniquely in the Customers table.
With the two tables joined in this way you append the CustomerID form the
new Customers table and the columns form Master list which you want to go
into the Orders table. Once again you should create a unique index on a
suitable 'candidate key' of one or more columns in the Orders table which
prevents duplicate rows being appended from Master List.
Slightly more tricky is appending data into a table which models a
many-to-many relationship between two entities. An example might be an
OrderDetails table, which includes foreign key OrderID and ProductID columns
referencing the primary keys of Orders and Products. The principle is much
the same, however, as with the above. What you'd do is create an append
query which joins Master List to both the new Orders and Products table on
suitable candidate keys in each. You can then append the OrderID form Orders
and the ProductID from Products, along with any columns from MasterList, such
as Quantity, UnitPrice etc which you'd have in the OrderDetails table.
By working through all the entities involve (now represented as new tables)
and systematically appending data to each you should end up with a fully
normalized set of tables and a much more robust and efficient database.
Before embarking on this you should be reasonably familiar with the
principles of the database relational model, so I'd suggest that you spend
some time with a good book. Any good general purpose primer on Access should
give you a good grounding in the relational model, Also study the structure
of the sample Northwind database carefully. Its not 100% normalized (really
it should have separate Cities and Regions tables to eliminate the redundancy
in the Customers table for instance), but it’s a reasonable model to follow
as a starting point.
Finally BACKUP AND KEEP your Master List table safe somewhere. If you find
you've missed something when decomposing it you might need it!
