Access 97 - Table Relationships

[Guest]

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.

 

[Joseph Meehan]

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.

Let's start by helping me understand what you have. It sounds like it
may be a table design problem, maybe not.

You have two tables that you want to link.

There is more than one common field.

Let's talk about the fields. Would they be like Color, Size, Style...
So that you would need all three to establish a match? Would they be like
Date_of_1st_Sale, Date_of_2nd_Sale, Date_of_3rd_Sale ...?

If it is like the second example, you have a table design problem where
you are using different fields for the same data, when you should have
different records for each sale. That will need to be cleaned up first.

Since you really match records, not fields, am I correct in guessing
that you need all five fields to match records?

Do you have an equal number of records in each table?

 

[Guest]

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.

 

[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.

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

 

[Joseph Meehan]

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.

Something here. If the information is not relevant to the first table,
then why does it need to be updated when the first table is updated? If it
is the same data, there should only be ONE occurrence of the data in one
table.

What exactly is the relationship between the two tables?

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.

It sounds like a table design problem that needs to be resolved first.

 

[Guest]

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.

 

[Guest]

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.

 

[Guest]

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.

 

[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?

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?

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