Relationships

[Carla]

Hi, I am very confused about relationships. I have read and done the online
training but I am still struggling and hope someone can help me.

I have a database that tracks safety observations with several tables. The
main table is my OBS Main table, which contains the basic information about
each safety observation. The OBS_ID is my primary key I do believe. Now I
have 3 other tables - Counter Measures, Critical Behaviours, and Critical
Conditions. These tables link to my main table by each having the same
OBS_ID field in them. When I tried to create the relationship between the
tables from my main table, it creates a one to many relationship and I can't
seem to change it. I think it should be a 1 to 1 relationship - each record
in the OBS Main table may have 1 Counter measure, critical behaviour or
critical condition but no more. Am I right? If so, how do I change the
relationship to a one to one?

 

[John Spencer]

To answer the question:
If you have a unique index on the OBS_ID field in the main table and a unique
index on the OBS_ID field in the other tables then you should be able to get a
one-to-one relationship. Otherwise, you are going to end up with a
one-to-many relationship.

I am curious why you need the additional tables if this is going to be a
one-to-one relationship - unless you have a situation where the other tables
have many fields and there may or may not be a record associated with the main
table, so you are sub-classing the additional information.

John Spencer
Access MVP 2002-2005, 2007-2010
The Hilltop Institute
University of Maryland Baltimore County

 

[Carla]

you probably have good reason to be curious. I'm just learning this as I go
along and it's been a challenge. I think it's the latter - not always is
there a counter measure etc. I am trying to recreate a database that was
built by someone much more skilled who has left our organization. When he
left, he took his toys and went home so to speak. I can look at various
areas of the old database but not change anything. So I based my tables on
what he had set up.

 

[John W. Vinson]

I think it should be a 1 to 1 relationship - each record
in the OBS Main table may have 1 Counter measure, critical behaviour or
critical condition but no more. Am I right? If so, how do I change the
relationship to a one to one?

Just in terms of business rules, I think this is at best rather odd. Surely
any safety issue would have zero, one, *OR MORE* countermeasures! For example,
"Flammable materials stored in wood closet in warehouse" might need:

1. Move materials to metal storage unit
2. Provide employee training in safe storage
3. Install appropriate fire extinguishers near closet
4. ...

Do you really want to restrict ANY of these to one, and only one, option?

 

[Carla]

yes you are right. I had my countermeasure field set to memo but the way you
are suggesting makes much more sense. Back to the drawing board......

thanks.

 

[Carla]

OK, I am really confused now. I went and deleted all my one to one
relationships and tried to recreate them (dragging from main table to other
tables in the relationships window). Now Access just wants to create One to
One relationships and I have tried editing them to One to Many by
doubleclicking on the line and modifying the relationship type but it doesn't
seem to actually change it. What do I do now? It keeps getting worse!

 

[Douglas J. Steele]

Access makes it a 1-to-1 relationship when you relate the primary key of one
table to the primary key of another table. It'll make it a 1-to-many
relationship otherwise.

 

[John W. Vinson]

OK, I am really confused now. I went and deleted all my one to one
relationships and tried to recreate them (dragging from main table to other
tables in the relationships window). Now Access just wants to create One to
One relationships and I have tried editing them to One to Many by
doubleclicking on the line and modifying the relationship type but it doesn't
seem to actually change it. What do I do now? It keeps getting worse!

The relationship is one to one because you're joining from a field with a
unique index to another field also with a unique index (for example, joining a
Primary Key to another Primary Key).

The "parent" table joining field should be the primary key. The field you're
joining to in the child table must NOT be the primary key, and should not have
a unique index; you should be joining to some *other* field, of the same
datatype as the parent field (Long Integer if the parent primary key is an
autonumber).