Access 97 - Table Relationships

W

Working girl

I have two tables in Access. One, the source table, includes all the
identifying information for the files. The second table will be used to
track detailed descriptions of the status of the files. The information
about the files needs to be reflected from the source table to the second
table. Not all of the fields are matching, but the fields that do match need
to be connected, so that the referencial integrity function can update the
second table when the source table changes.

This requires many one-to-one relationships. I have looked everywhere and I
am not able to find out how to manage this. There are at least 5 common
fields that need to be linked from the source table to the secondary table.

Please help.
 
W

working girl

The first table is used to hold information about files. The details include
file number, file name, start date, project manager and end date.

The secondary table is to be used for status tracking. The information in
that table is not relevant to the first table, but will be useful to run
queries for the project manager's status meetings. Therefore, if the data
about the file is updated in the first table, it needs to be reflected in the
secondary table.

How is it possible to link these fields with referential integrity, yet have
them serve seperate function and include seperate info? Everyone I have
spoken to says it's possible, but they do not know how to manage it.
 
J

John Vinson

On Fri, 3 Dec 2004 11:55:04 -0800, working girl <working
The first table is used to hold information about files. The details include
file number, file name, start date, project manager and end date.

The secondary table is to be used for status tracking. The information in
that table is not relevant to the first table, but will be useful to run
queries for the project manager's status meetings. Therefore, if the data
about the file is updated in the first table, it needs to be reflected in the
secondary table.
Click to expand...

You should almost certainly NOT store any information about files in
the status table, other than a Foreign Key linked to the Files table.
How are Files related to Status meetings, in the real world of the
office? Does each meeting deal with one and only one file? Or might
each file be involved with several status meetings, and each meeting
deal with several files? Or is the link via the project manager?

In your relational database, you should NOT store data redundantly.
Instead, just store a link to the data. Information about a file
should be stored *only* in the table of Files; you would use queries
to link to it if it "needs to be reflected" rather than copying the
data.

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

working girl

Maybe if I explain it more clearly:

The first table, the files are listed with all identifying information. If
a file name or number is updated, it would need to be reflected on the status
database. If a file start or end date is updated, it would need to be
reflected on the database.

You mentioned redundant data - are you suggesting that only one incarnation
of this identifying information be coded, and a query can be run on both
databases? The referential integrity would therefore link the "status"
database to the "file" database by sole virtue of the file number?
Subsequently, for reports and the like, would a query be run from both tables
to build a report that is inclusive of all prevalent information?

This is the first case of database relationships we have instituted in our
work environment. I may have created this relationship incorrectly. Please
advise an appropriate course of action.
 
W

working girl

Maybe if I explain it more clearly:

The first table, the files are listed with all identifying information. If
a file name or number is updated, it would need to be reflected on the status
database. If a file start or end date is updated, it would need to be
reflected on the database.

You mentioned redundant data - are you suggesting that only one incarnation
of this identifying information be coded, and a query can be run on both
databases? The referential integrity would therefore link the "status"
database to the "file" database by sole virtue of the file number?
Subsequently, for reports and the like, would a query be run from both tables
to build a report that is inclusive of all prevalent information?

This is the first case of database relationships we have instituted in our
work environment. I may have created this relationship incorrectly. Please
advise an appropriate course of action.
 
W

working girl

Maybe if I explain it more clearly:

The first table, the files are listed with all identifying information. If
a file name or number is updated, it would need to be reflected on the status
database. If a file start or end date is updated, it would need to be
reflected on the database.

You mentioned redundant data - are you suggesting that only one incarnation
of this identifying information be coded, and a query can be run on both
databases? The referential integrity would therefore link the "status"
database to the "file" database by sole virtue of the file number?
Subsequently, for reports and the like, would a query be run from both tables
to build a report that is inclusive of all prevalent information?

This is the first case of database relationships we have instituted in our
work environment. I may have created this relationship incorrectly. Please
advise an appropriate course of action.
 
J

John Vinson

On Mon, 6 Dec 2004 07:45:04 -0800, working girl <working
You mentioned redundant data - are you suggesting that only one incarnation
of this identifying information be coded, and a query can be run on both
databases?
Click to expand...

Exactly. That's how reoational databases work.
The referential integrity would therefore link the "status"
database to the "file" database by sole virtue of the file number?
Correct.

Subsequently, for reports and the like, would a query be run from both tables
to build a report that is inclusive of all prevalent information?
Click to expand...

That's exactly right.

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