same field in more than one table

[Emelda]

I am new to access database design. My database is for tracking documents
(creation, revision, and filing) and then tracking training. Since I am
working with existing data for tracking documents, I am concentrating on
making sure this works first before including training. This existing data is
located in separate access databases that function more like excel
spreadsheets. I have combined them into tables within one database, performed
the normalization steps, created a junction table for the primary keys of the
individual tables, and created relationships from the primary keys of the
individual tables to the junction table.

I am having trouble with one field (Document Number and Revision). It is in
three tables (creation, filing, and the junction tables). In the creation
table, Document Number and Revision field is the primary key. In the filing
and junction tables, the Document Number and Revision field is a foreign key.
I was unable to create a relationship between the creation table and the
filing table and enable referential integrity. Therefore, I created a
one-to-many relationship for Document Number and Revision field from the
creation table to the junction table. Is this sufficient normalization? Also,
is this one relationship sufficient to link the data?

There will not be equivalent number of records in each of these tables. The
filing table will include the most records.

I have questions regarding forms. I would like to be able to see some
identifying information (at minimum, the Document Number and Revision,
Document Title, Document Effective Date and Change Number) on the form
regardless of which stage it was entered. Since the normalization process
occurred, I can pull the Document Title and Effective Date from the filing
database and the Change Number from the revision table.

I run into problems with the Document Number and Revision field since it is
located in two tables (creation and filing). Document Number and Revision
cannot be the primary key in the filing table since not all documents filed
have a Document Number and Revision. It is nice to have it in the creation
table as the primary key so as to prevent any inadvertent duplication of
entries in the Document Number and Revision field.

Any help is greatly appreciated.

 

[Fred]

Hello Emelda,

Looks like you've been doing a lot of studying of Access. My
recommendation is.......

From what you have and haven't told us, you haven't told us the important
stuff neede to try to answer your question, which is the results from "Step
1" and "Step2", and my guess is that you skipped those steps.

Step one is to shut the computer off and list and define the real world
entities that you want to database, and the real world relationships between
them that you want to (record in) the database

Step two (with the computer still off) is to rough out a table structure and
linkages that implement what you decided in step one. (just general temrs,
not Access details

Step 3 is creating a table structure in Access which implements what you
decided in #1 and #2.


Steps 4 on is getting some data into it and making queries, forms, reporst
etc. that do what you need.

 

[Piet Linden]

Hello Emelda,

Looks like you've been doing a lot of studying of Access.     My
recommendation is.......

From what you have and haven't told us, you haven't told us the important
stuff neede to try to answer your question, which is the results from "Step
1" and "Step2", and my guess is that you skipped those steps.    

Step one is to shut the computer off and list and define the real world
entities that you want to database, and the real world relationships between
them that you want to (record in) the database

Step two (with the computer still off) is to rough out a table structure and
linkages that implement what you decided in step one.    (just general temrs,
not Access details

Step 3 is creating a table structure in Access which implements what you
decided in #1 and #2.

Steps 4 on is getting some data into it and making queries, forms, reporst
etc. that do what you need.

One thing I would add to Fred's excellent guide is to make a list of
questions you need the database to answer. Also, draw a diagram of
the tables with the essential fields. Then you can look at the
diagram and see for yourself that the structure will be adequate to
answer the questions you posed. If you don't do that before you start
building, you may have to go back and redo/fix parts of your DB. Not
a huge deal when your database is simple, but the more complex it is,
the more I find this kind of exercise really helps.