One to One Relationship?

[Peter]

Hello,

I appreciate your interest in my question. Here it is. I
created two tables and set them up as a one to one
relationship on a specific key. When I try to add a
record to one table it complains about the record does
not exist in the other table. How does a one to one
relationship work? If I have two tables with similar
records and I delete one record the matching record in
the other table gets deleted but how do you add a record
to both tables at the same time?

Peter

 

[Ken Snell]

Set up a query that is based on both tables, joining the tables using the
one-to-one key, and use that query as the recordsource of a form. You then
can enter values through the form.

Or

Set up a form whose recordsource is a query based on the first table, and
that has a subform whose recordsource is a query based on the second table.
Set the subform's Link Child Fields and Link Master Fields to the name of
the linking field. Then, you can enter values into the first table, and then
into the second table.

Note that a one-to-one relationship is not overly common; usually used when
you're restricting some users from seeing/accessing some types of data. Are
you sure you need to use a one-to-one relationship here?

 

[Tim Ferguson]

How does a one to one relationship work?

It is pretty much the same as any other relationship: one table has a field
that can only accept values that exist as PKs in the other table. The
difference is that each entry in the first table must be different, so that
each record in the other table can only be referenced once (or not at all).

If I have two tables with similar
records and I delete one record the matching record in
the other table gets deleted but how do you add a record
to both tables at the same time?

Hmmm: the point about relating tables is that the records should utterly
different, for example Customers and BankAccounts; or People and Salesmen.
These would represent situations where 1:1 relationships are required.

Not all Customers would have a BankAccount record, but the ones that do
would only have one account (the rest would be cash customers, for
example).

A table of People (name, address, birthday) would have matching Salesmen
records (department, lastmonthtotal, etc) as well as Administrators and
ClericalStaff and FrontOfHouse and so on -- but each Salesman would only
relate to one People record, being only one person.

As Ken says, true 1:1 relationships are pretty uncommon and most of the
time questions here about them are due to misunderstanding, particularly
when they are about creating a record automatically. Do post back with more
details of what you are trying to achieve.

Best wishes


Tim F

 

[Peter]

Tim and Ken,

Thanks for reply. I am new using Access as well as
relationships but I think I understand the basics. I
have a table for staff specific info and I have their
contact info in another table. What I was trying to
achieve is have the job information for a person in one
table and have their personal contact information in
another table. With a one-to-one, I thought if I delete
the one it will delete the other. The person would never
have two jobs (one-to-many). But when I setup the one-to-
one relationship and tried to enter the person's job
information in one table but Access won't let me save it
because it doesn't exist in the other table. I
understand why but I can only type in one record at a
time. How do you get two records to arrive into two
tables at once?

Thanks
Peter

 

[Ken Snell]

My reply tells you how to do this via a form. You cannot do it in the table
directly unless you use subdatasheets (not recommended for serious data
entry -- always use a form for data entry).

 

[Tim Ferguson]

What I was trying to
achieve is have the job information for a person in one
table and have their personal contact information in
another table.

Okay: this is roughly a method called subtyping. I am assuming that
everyone has ContactInformation but only some of them have JobInformation.

In that case, you need to allocate the PK value to ContactInformation and
complete that record first. When you want to allocate a job to the person,
you then create the JobInformation record using the PK from the
ContactInformation record.

With a one-to-one, I thought if I delete
the one it will delete the other.

I am a big non-believer in cascading deletes. In the wrong hands it can
wipe out an awful lot of information in a single stroke, but I know that
other people think it's helpful.

The person would never
have two jobs (one-to-many).

So you put a Unique Index on the JobInformation.ContactID field, or even
make it the PK of the job table.

But when I setup the one-to-
one relationship and tried to enter the person's job
information in one table but Access won't let me save it
because it doesn't exist in the other table.

See my very first comment. One-to-one relationships have asymmetrical ends,
and you have to create the records in the right order. As long as you have
the design right, then this is simple and logical and obvious -- if there
is some confusion about which comes first then it's prolly time to go back
to the drawing board and reconsider the entities.

And as for using table datasheets, see Ken's response or in other words
Just Don't Do It.

Hope that helps


Tim F

 

[Peter]

Tim and Ken,

This is seems abit complicated. I didn't find any help
on subtyping in Access's help or knownledge base. Are
there any good books that would discuss this?

Also, how do I setup a query on a form when I do not have
any data so far? What would it be querying - the
database is empy.

Is it still considered one-to-one if 3 out of my 5
contacts have a job info record?

Peter

-----Original Message-----


Okay: this is roughly a method called subtyping. I am assuming that
everyone has ContactInformation but only some of them have JobInformation.

In that case, you need to allocate the PK value to ContactInformation and
complete that record first. When you want to allocate a job to the person,
you then create the JobInformation record using the PK from the
ContactInformation record.


I am a big non-believer in cascading deletes. In the wrong hands it can
wipe out an awful lot of information in a single stroke, but I know that
other people think it's helpful.


So you put a Unique Index on the

JobInformation.ContactID field, or even

 

[Tim Ferguson]

This is seems abit complicated. I didn't find any help
on subtyping in Access's help or knownledge base. Are
there any good books that would discuss this?

Subtyping or subclassing is not so much a distinct method as a solution to
a typical scenario. "Designing relational database systems" by our own
Rebecca Riordan[1] discusses it explicitly, but it will be there in all
standard db design books as part of the introduction to normalisation and
entity modelling, even if it's not called as such.

Also, how do I setup a query on a form when I do not have
any data so far? What would it be querying - the
database is empy.

For design and testing purposes, it is usual to create some dummy data --
you would never trust an untested app on real live data anyway. ("Oops, I
forgot to cancel that delete command...")

Is it still considered one-to-one if 3 out of my 5
contacts have a job info record?

Strictly speaking, it's a one-to-(none-or-one) but it doesn't matter about
the ratio. I am wondering, however, if you would not be better off with a
single table with all the fields. How many other types of ContactInfo[2]
will you have apart from JobInfo?

[1] Highly recommended, by the way, although I heard a rumour that it is
out of print. New edition, Rebecca?

[2] A word about table names: most designers prefer to name tables after
the _things_ they represent, rather than the _functions_ they serve.
Suggest Contacts and Employees, for example. It's a matter of personal
preference so do not get too anxious about it, but it probably helps to
keep clear what should be in the table.

Hope that helps


Tim F

 

[Joan Wild]

There is also a sample database and explanation by Rebecca at
http://www.mvps.org/access/tables/tbl0013.htm

--
Joan Wild
Microsoft Access MVP

This is seems abit complicated. I didn't find any help
on subtyping in Access's help or knownledge base. Are
there any good books that would discuss this?

Subtyping or subclassing is not so much a distinct method as a solution to
a typical scenario. "Designing relational database systems" by our own
Rebecca Riordan[1] discusses it explicitly, but it will be there in all
standard db design books as part of the introduction to normalisation and
entity modelling, even if it's not called as such.

Also, how do I setup a query on a form when I do not have
any data so far? What would it be querying - the
database is empy.

For design and testing purposes, it is usual to create some dummy data --
you would never trust an untested app on real live data anyway. ("Oops, I
forgot to cancel that delete command...")

Is it still considered one-to-one if 3 out of my 5
contacts have a job info record?

Strictly speaking, it's a one-to-(none-or-one) but it doesn't matter about
the ratio. I am wondering, however, if you would not be better off with a
single table with all the fields. How many other types of ContactInfo[2]
will you have apart from JobInfo?

[1] Highly recommended, by the way, although I heard a rumour that it is
out of print. New edition, Rebecca?

[2] A word about table names: most designers prefer to name tables after
the _things_ they represent, rather than the _functions_ they serve.
Suggest Contacts and Employees, for example. It's a matter of personal
preference so do not get too anxious about it, but it probably helps to
keep clear what should be in the table.

Hope that helps


Tim F