More than two related tables seems to affect all relationships

I have 3 tables that I want to relate/link. When I link 2 of them, all's
seemingly good. When I introduce that 3rd table, it messes up the other two
and doesn't work right itself. Is there some kind of validation procedure
I'm supposed to do? For what it's worth, the linked fields are the same
name, type, and size and constitute a one-to-one relationship. Help!

On Fri, 13 Jul 2007 11:34:03 -0700, Je''farc
wrote:

I have 3 tables that I want to relate/link. When I link 2 of them, all's
seemingly good. When I introduce that 3rd table, it messes up the other two
and doesn't work right itself. Is there some kind of validation procedure
I'm supposed to do? For what it's worth, the linked fields are the same
name, type, and size and constitute a one-to-one relationship. Help!

One to one relationships are QUITE uncommon. If you're not either Subclassing
or using Table-driven field level security, or if neither term rings a bell,
you probably should NOT be using one to one relationships.

Since you chose not to post any information about the tables, or the "messes"
that result, or your expectations, it's hard to give specific advice - but
general advice would be that you probably DON'T want to do things this way.

What is the nature of the tables? What real-life entity (thing, person or
event) does each table represent? What do you expect from the relationship?
All a relationship does is to PREVENT the addition of "orphan" records; if
you're expecting new records to be automagically created, you'll be
disappointed!

John W. Vinson [MVP]

My challenge is to track (land use) development proposals for my
organization. To that end I'm keeping a variety of info, including
application number (primary field), applicant name, address, application
type, notes, outcome, etc. Make that Table1. Two other tables track
specific pieces of info (conditions of approval and tree replacement
requirements) for some/certain applications (Table2 and 3). Currently Table1
is kept in a table in a Word document of 117 pages (and growing) at close to
2 MB. It seems to me a terrible way to track this kind of info. I thought a
db would be a more efficient way store this info and would allow me to tie
Table1 to Tables2 and 3 based on the application number (i.e., I thought I
could establish relationships between the three tables). At this time, any
record in any of the tables would be unique; hence, the presumed one-to-one
relationship. There are other types of development proposals that should be
tracked, too, but they have their own unique ID (primary field). While the
application numbers are my primary fields, one could make an argument that
the parcel ID is a more universal primary since it could be applied to all
applications (not just Table1) (i.e., all proposals have a location). The
problem with that logic is that some applications utilize multiple parcels
and parcels are divided over time.

The bottom line is that I need a single db that tracks all this info and
allows queries and reports. I need to develop some kind of tracking resource
that allows the user to find, in one place, EVERYTHING there is to know about
an application. It's entirely possible that I've not taken the right
approach, but I don't know what else to do.

"John W. Vinson" wrote:

On Fri, 13 Jul 2007 11:34:03 -0700, Je''farc
wrote:

I have 3 tables that I want to relate/link. When I link 2 of them, all's
seemingly good. When I introduce that 3rd table, it messes up the other two
and doesn't work right itself. Is there some kind of validation procedure
I'm supposed to do? For what it's worth, the linked fields are the same
name, type, and size and constitute a one-to-one relationship. Help!

One to one relationships are QUITE uncommon. If you're not either Subclassing
or using Table-driven field level security, or if neither term rings a bell,
you probably should NOT be using one to one relationships.

Since you chose not to post any information about the tables, or the "messes"
that result, or your expectations, it's hard to give specific advice - but
general advice would be that you probably DON'T want to do things this way.

What is the nature of the tables? What real-life entity (thing, person or
event) does each table represent? What do you expect from the relationship?
All a relationship does is to PREVENT the addition of "orphan" records; if
you're expecting new records to be automagically created, you'll be
disappointed!

John W. Vinson [MVP]

On Mon, 16 Jul 2007 05:02:02 -0700, Je''farc
wrote:

My challenge is to track (land use) development proposals for my
organization. To that end I'm keeping a variety of info, including
application number (primary field), applicant name, address, application
type, notes, outcome, etc. Make that Table1. Two other tables track
specific pieces of info (conditions of approval and tree replacement
requirements) for some/certain applications (Table2 and 3). Currently Table1
is kept in a table in a Word document of 117 pages (and growing) at close to
2 MB. It seems to me a terrible way to track this kind of info.

You'll get no argument in this Access group on that assertion! g

I thought a
db would be a more efficient way store this info and would allow me to tie
Table1 to Tables2 and 3 based on the application number (i.e., I thought I
could establish relationships between the three tables). At this time, any
record in any of the tables would be unique; hence, the presumed one-to-one
relationship.

That suggests that you're saying that each proposal will include one and only
one condition of approval, and one and only one tree replacement requirement.
Is that accurate?

Also... about the applicants. Can a proposal ever be submitted by TWO people?
or even more? Perhaps that's another table. Or can one person ever submit two
or more proposals? Perhaps that's a many to many relationship right there!

There are other types of development proposals that should be
tracked, too, but they have their own unique ID (primary field). While the
application numbers are my primary fields, one could make an argument that
the parcel ID is a more universal primary since it could be applied to all
applications (not just Table1) (i.e., all proposals have a location). The
problem with that logic is that some applications utilize multiple parcels
and parcels are divided over time.

So you have a many to many relationship from Parcels to Proposals, and even
perhaps from parcels to parcels.

The bottom line is that I need a single db that tracks all this info and
allows queries and reports. I need to develop some kind of tracking resource
that allows the user to find, in one place, EVERYTHING there is to know about
an application. It's entirely possible that I've not taken the right
approach, but I don't know what else to do.

Access is certainly a better way than Word to do this, but I don't think
you're yet taking full advantage of the relational capbilities of Access.

John W. Vinson [MVP]

Many thanks for the feedback. It sounds like I may have a much bigger design
issue to resolve.

"John W. Vinson" wrote:

On Mon, 16 Jul 2007 05:02:02 -0700, Je''farc
wrote:

My challenge is to track (land use) development proposals for my
organization. To that end I'm keeping a variety of info, including
application number (primary field), applicant name, address, application
type, notes, outcome, etc. Make that Table1. Two other tables track
specific pieces of info (conditions of approval and tree replacement
requirements) for some/certain applications (Table2 and 3). Currently Table1
is kept in a table in a Word document of 117 pages (and growing) at close to
2 MB. It seems to me a terrible way to track this kind of info.

You'll get no argument in this Access group on that assertion! g

I thought a
db would be a more efficient way store this info and would allow me to tie
Table1 to Tables2 and 3 based on the application number (i.e., I thought I
could establish relationships between the three tables). At this time, any
record in any of the tables would be unique; hence, the presumed one-to-one
relationship.

That suggests that you're saying that each proposal will include one and only
one condition of approval, and one and only one tree replacement requirement.
Is that accurate?

Also... about the applicants. Can a proposal ever be submitted by TWO people?
or even more? Perhaps that's another table. Or can one person ever submit two
or more proposals? Perhaps that's a many to many relationship right there!

There are other types of development proposals that should be
tracked, too, but they have their own unique ID (primary field). While the
application numbers are my primary fields, one could make an argument that
the parcel ID is a more universal primary since it could be applied to all
applications (not just Table1) (i.e., all proposals have a location). The
problem with that logic is that some applications utilize multiple parcels
and parcels are divided over time.

So you have a many to many relationship from Parcels to Proposals, and even
perhaps from parcels to parcels.

The bottom line is that I need a single db that tracks all this info and
allows queries and reports. I need to develop some kind of tracking resource
that allows the user to find, in one place, EVERYTHING there is to know about
an application. It's entirely possible that I've not taken the right
approach, but I don't know what else to do.

Access is certainly a better way than Word to do this, but I don't think
you're yet taking full advantage of the relational capbilities of Access.

John W. Vinson [MVP]