One-to-One Relationship

How can I make the relationship between two tables as one-to-one. I was
able to make a one-to-many, but I am trying to change it to one-to-one

Thank You

msw wrote in message ...

How can I make the relationship between two tables as one-to-one. I was
able to make a one-to-many, but I am trying to change it to one-to-one

You'll have one table that is the "master" (the "authoritative source" for
the key field) and another that is the "related" or "child" table (with a
foreign key field referring to the key field in the "master"). If both the
key field and the corresponding foreign key field are marked as Indexed (No
duplicates), you force a one-to-one relationship when you join them on those
fields.

There are other ways, but that is a simple one.

Larry Linson
Microsoft Access MVP

On Tue, 27 Dec 2005 21:39:08 -0500, msw wrote:

How can I make the relationship between two tables as one-to-one. I was
able to make a one-to-many, but I am trying to change it to one-to-one

Before you do... check into whether you should.

One to one relationships are quite uncommon. If you're not
Subclassing, or using Table-Based Field Level Security (or are
unfamiliar with those terms), I wonder whether you really *need* such
a relationship. If you're relating two tables because you're pushing
the 255 field limit (or the often more stringent 2000 byte-per-record)
limit on tables, then your table design is almost certainly WRONG.

To get a one to one, there must be a unique Index (such as a primary
key, though that's not essential) on the joining fields in both
tables. But do post a description of the tables, and indicate why you
can't just incorporate all the fields into one table.

John W. Vinson[MVP]

I have found 1:1 relationships very useful on numerous ocassions - usually
for what you call subclassing, which I have encountered frequently. In
English, what this means is that the majority of records have some number N
of atrributes (fields), but some records require additional attributes. A
pair of tables related 1:1 is much easier to manage that a single table with
a bunch of null fields.
--
Ted

I recommended one-to-one today where folks were recording certificates sold
by serial number and second table recording redemption of the same serial
numbers.

They could have used a singler table to record both but it was simpler for
data entry as recording was batch processed by entering start serial number
and ending serial number.

Post was "Adding a Range of Numbers from a form" FORMS DESIGN

"John Vinson" wrote:

On Tue, 27 Dec 2005 21:39:08 -0500, msw wrote:

How can I make the relationship between two tables as one-to-one. I was
able to make a one-to-many, but I am trying to change it to one-to-one

Before you do... check into whether you should.

One to one relationships are quite uncommon. If you're not
Subclassing, or using Table-Based Field Level Security (or are
unfamiliar with those terms), I wonder whether you really *need* such
a relationship. If you're relating two tables because you're pushing
the 255 field limit (or the often more stringent 2000 byte-per-record)
limit on tables, then your table design is almost certainly WRONG.

To get a one to one, there must be a unique Index (such as a primary
key, though that's not essential) on the joining fields in both
tables. But do post a description of the tables, and indicate why you
can't just incorporate all the fields into one table.

John W. Vinson[MVP]

On Wed, 28 Dec 2005 16:01:02 -0800, "KARL DEWEY"
wrote:

I recommended one-to-one today where folks were recording certificates sold
by serial number and second table recording redemption of the same serial
numbers.

They could have used a singler table to record both but it was simpler for
data entry as recording was batch processed by entering start serial number
and ending serial number.

Ted, Karl - good points both (and that was a new one to me, Karl).

My point wasn't that one to one relationships were *never* correct, or
even all that unusual - just that they are for special uses such as
subclassing, and that you should only use them if it's necessary to do
so. Many beginners jump to one-to-ones because of repeating fields or
misunderstandings of how relationships work, so I just wanted to give
a heads-up!

John W. Vinson[MVP]

Thank you all for your replies. Happy Holidays and Happy New Year!!!!!!

Unfortunately, I am not as experienced with access as all of you are. After
reading your recommendations, I am a little reluctant of which direction I
should go.

Here's what I am trying to do...

My hobby is woodworking and have a lot of magazines and books that I would
to keep track of the articles as well as other information within the
magazines and books that are of use.

The woodworking is very diversified and covers a lot of different subjects
that I would group together. I want to be able to classify the magazines
and books as well as the articles for quick lookup.

The database is a simple one. I created 5 tables. One for the Articles,
One for the Magazines, One for the Categories, One for the Subcategories, as
well as one for the Location.

The Article table has a unique key that is ArticleID (Numeric-Auto).
Magazine has a unique key MagazineID (Numeric-Auto), Category has a unique
key CategoryID (Numeric-Auto), Subcategory has a unique key SubcatID
(Numeric-Auto), Location has a unique key LocationId (Numeric-Auto).

Within the Article table, I have included all of the keys of the other
tables. I was using Article as the parent table.

Article would relate to Magazine and Magazine would relate to Location.
Article would also relate to category and category would relate to
subcategory. Each relationship is a one to many and I am trying to make it
a one-to-one??? After reading the responses, I am now unsure of my
decision.

I would like to search by article description, or category (Subject) or
subcategory. I would like to search a specific magazine for a subject of my
choice, since some magazines as well as books covers a specific subject and
other that type of subject.

Any feedback would be greatly appreciated.

Thank you again!!! and....

Happy Holidays and Happy New Year!!!!!!



"Larry Linson" wrote in message
...
msw wrote in message ...

How can I make the relationship between two tables as one-to-one. I was
able to make a one-to-many, but I am trying to change it to one-to-one

You'll have one table that is the "master" (the "authoritative source" for
the key field) and another that is the "related" or "child" table (with a
foreign key field referring to the key field in the "master"). If both the
key field and the corresponding foreign key field are marked as Indexed
(No duplicates), you force a one-to-one relationship when you join them on
those fields.

There are other ways, but that is a simple one.

Larry Linson
Microsoft Access MVP

On Wed, 28 Dec 2005 20:28:08 -0500, "msw" wrote:


Here's what I am trying to do...

My hobby is woodworking and have a lot of magazines and books that I would
to keep track of the articles as well as other information within the
magazines and books that are of use.

The woodworking is very diversified and covers a lot of different subjects
that I would group together. I want to be able to classify the magazines
and books as well as the articles for quick lookup.

I presume that each article (and each magazine) would be classed under
*multiple* categories? and that each category would pertain to
*multiple* articles? Or do you want each article to belong to one and
only one category?

The database is a simple one. I created 5 tables. One for the Articles,
One for the Magazines, One for the Categories, One for the Subcategories, as
well as one for the Location.

The Article table has a unique key that is ArticleID (Numeric-Auto).
Magazine has a unique key MagazineID (Numeric-Auto), Category has a unique
key CategoryID (Numeric-Auto), Subcategory has a unique key SubcatID
(Numeric-Auto), Location has a unique key LocationId (Numeric-Auto).

Within the Article table, I have included all of the keys of the other
tables. I was using Article as the parent table.

Article would relate to Magazine and Magazine would relate to Location.
Article would also relate to category and category would relate to
subcategory. Each relationship is a one to many and I am trying to make it
a one-to-one??? After reading the responses, I am now unsure of my
decision.

I would like to search by article description, or category (Subject) or
subcategory. I would like to search a specific magazine for a subject of my
choice, since some magazines as well as books covers a specific subject and
other that type of subject.

I would suggest adding two more tables:

ArticleSubjects
ArticleID link to Articles
SubjectID link to Categories

BookSubjects
same drill

Remove the SubjectID from the articles table (unless, of course, you
want to select exactly one and only one subject for each article).

Your Form (and yes, you *must* use a Form for this complex a database)
with a Subform based on ArticleSubjects. You can then enter as many
categories as you wish.

John W. Vinson[MVP]