If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
same field in more than one table
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. |
#2
|
|||
|
|||
same field in more than one table
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. |
#3
|
|||
|
|||
same field in more than one table
On Jul 17, 12:54*pm, Fred wrote:
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. |
Thread Tools | |
Display Modes | |
|
|