one-to-one relationships

[Gail C.]

I am trying to set up a one-to-one relationship to divide a large table with
many fields (about 75) and use subsets of the main table. I have the
database set up into 4 tables. I set up relationships with deferential
integrity using the AutoNumber ID. When I input from the Excel spreadsheet,
the date only inputs into one table. How do I connect the tables so I can
view the same info in all tables? Thanks.

 

[John W. Vinson]

I am trying to set up a one-to-one relationship to divide a large table with
many fields (about 75) and use subsets of the main table. I have the
database set up into 4 tables. I set up relationships with deferential
integrity using the AutoNumber ID. When I input from the Excel spreadsheet,
the date only inputs into one table. How do I connect the tables so I can
view the same info in all tables? Thanks.

Sorry, but you're totally misunderstanding what one to one relationships do.

They are NOT a way to store the same data redundantly in four different
tables. Instead, they are a way to link two tables - containing DIFFERENT data
- linked by some ID which is unique within each table. In practice, one to one
relationships are quite rare; normally you can just incorporate all the fields
into one table, and then use SELECT queries to select only a subset of the
fields (and/or a subset of the records) if that's what you want to see.

75 fields is indeed awfully wide for a relational table, and the data probably
should be normalized into one or more one-to-many (*NOT* one to one)
relationships.

Perhaps you could post a description of the nature of the data (some
representative fieldnames), and how these four subsets are defined; someone
may be able to suggest a properly normalized design.

 

[Jeff Boyce]

Gail

A common reason for establishing one-to-one relationships among tables is
"subtyping", where everything is, let's say, an "Entity", but some entities
are humans (with FirstName , LastName, DOB, ....), some entities are
organizations (with OrgName, ...), some entities are ... (you get the idea).

If the only reason you are trying to create multiple tables is to divide up
many fields, you don't need to do that.

On the other hand, a table with 75 fields is likely not well-normalized, so
you won't get very efficient use of Access' relationally-oriented
features/functions.

If you'll post back a description of what fields you have (not exhaustive,
just descriptive), the folks here may be able to offer alternate approaches.

--
Regards

Jeff Boyce
www.InformationFutures.net

Microsoft Office/Access MVP


Microsoft IT Academy Program Mentor
http://microsoftitacademy.com/