Relationship feature/bug/accident

I have a strange (to me, anyway) relationship setup that I haven't found
addressed in any of the forums, online help or any of my manuals. Of course,
maybe I'm just being dumb.

The basic structure tracks professional literature for a department's
internal library. There are three types of publications collected - books,
magazines and journals. There is one primary table for all the data which is
common to each type (archive number, physical location, date acquired...)
and a separate sub-table for each of the three types, containing data which
is unique to the type (books have an author, magazines do not...)

Originally I had a primary key in the main table using an autonumber field
and primary keys in the subtables using a compound key of consisting of the
autonumber from the main table as a standard doubleprecision number and
another autonumber field in the subtable. Access linked these up in three
one-to-many relationships and everything looked great.

Recently I realized that the autonumber fields in the subtables don't really
do anything for me, since each subtable can have either one or no records
for each record in the main table. That is, each piece of literature has
exactly one entry in the main table and exactly one entry in exactly one of
the three subtables. So carrying over the autonumber field from the main
table is enough to uniquely identify the record. With that in mind, I
removed the autonumber field from the subtables, leaving only the copy of
the autonumber from the main table as the primary key. The relationship
window still showed the links as one-to-many and everything I've written to
date still worked.

(Sorry to be so long-winded, but I want to make sure I explain this
properly.)

So here's the issue: When I delete such a relationship line, I can't put it
back as anything other than a one-to-one relationship. I don't want a strict
one-to-one, because for every main record, two of the three tables will have
no entry. But I'm leery of anything that smacks of witchcraft and a
relationship that can't be created without 'fooling' the table with a false
structure which is then removed again doesn't strike me as good practice.

Is this correct behavior for the relationship window or have I discovered
something weird? Can I set up a one-to-zero-or-one relationship somehow? I
assumed that my form would simply create one record on the many side of the
join and not address the fact that the structure could allow more than one.
Or have I made a fundamental mistake in the design somewhere?

Pete

P.S. I'll be out of the office until the weekend, at least.

This e-mail address is fake to keep spammers and their auto-harvesters out
of my hair. If you need to get in touch personally, I am 'pdanes' and I use
Yahoo mail. But please use the newsgroups whenever possible, so that all may
benefit from the exchange of ideas.

Pete,

These are one-to-one relationships. Simple as that. What you have done
is entirely appropriate.

I don't quite understand your comment "I don't want a strict one-to-one,
because for every main record, two of the three tables will have no
entry." The fact that two of the three "subtables" will not contain a
record corresponding with any given main table record, is really immaterial.

--
Steve Schapel, Microsoft Access MVP


Peter Danes wrote:
I have a strange (to me, anyway) relationship setup that I haven't found
addressed in any of the forums, online help or any of my manuals. Of course,
maybe I'm just being dumb.

The basic structure tracks professional literature for a department's
internal library. There are three types of publications collected - books,
magazines and journals. There is one primary table for all the data which is
common to each type (archive number, physical location, date acquired...)
and a separate sub-table for each of the three types, containing data which
is unique to the type (books have an author, magazines do not...)

Originally I had a primary key in the main table using an autonumber field
and primary keys in the subtables using a compound key of consisting of the
autonumber from the main table as a standard doubleprecision number and
another autonumber field in the subtable. Access linked these up in three
one-to-many relationships and everything looked great.

Recently I realized that the autonumber fields in the subtables don't really
do anything for me, since each subtable can have either one or no records
for each record in the main table. That is, each piece of literature has
exactly one entry in the main table and exactly one entry in exactly one of
the three subtables. So carrying over the autonumber field from the main
table is enough to uniquely identify the record. With that in mind, I
removed the autonumber field from the subtables, leaving only the copy of
the autonumber from the main table as the primary key. The relationship
window still showed the links as one-to-many and everything I've written to
date still worked.

(Sorry to be so long-winded, but I want to make sure I explain this
properly.)

So here's the issue: When I delete such a relationship line, I can't put it
back as anything other than a one-to-one relationship. I don't want a strict
one-to-one, because for every main record, two of the three tables will have
no entry. But I'm leery of anything that smacks of witchcraft and a
relationship that can't be created without 'fooling' the table with a false
structure which is then removed again doesn't strike me as good practice.

Is this correct behavior for the relationship window or have I discovered
something weird? Can I set up a one-to-zero-or-one relationship somehow? I
assumed that my form would simply create one record on the many side of the
join and not address the fact that the structure could allow more than one.
Or have I made a fundamental mistake in the design somewhere?

Pete

P.S. I'll be out of the office until the weekend, at least.

This e-mail address is fake to keep spammers and their auto-harvesters out
of my hair. If you need to get in touch personally, I am 'pdanes' and I use
Yahoo mail. But please use the newsgroups whenever possible, so that all may
benefit from the exchange of ideas.

On Tue, 6 Sep 2005 19:30:08 +0200, "Peter Danes"
wrote:

I have a strange (to me, anyway) relationship setup that I haven't found
addressed in any of the forums, online help or any of my manuals. Of course,
maybe I'm just being dumb.

Well, true Subclassing - which you're using, and a classic case of it
- is somewhat tricky. You've got it almost right though.

The basic structure tracks professional literature for a department's
internal library. There are three types of publications collected - books,
magazines and journals. There is one primary table for all the data which is
common to each type (archive number, physical location, date acquired...)
and a separate sub-table for each of the three types, containing data which
is unique to the type (books have an author, magazines do not...)

Subclassing in its classic form: an Entity with subclasses, where all
subclasses have some attributes in common but each subclass has other
attributes unique to that class. Strictly speaking, the term "one to
one" is shorthand for "One to (zero or one)", since the child table
Magazines will not contain any record where the main table refers to a
Book.

Originally I had a primary key in the main table using an autonumber field
and primary keys in the subtables using a compound key of consisting of the
autonumber from the main table as a standard doubleprecision number and
another autonumber field in the subtable. Access linked these up in three
one-to-many relationships and everything looked great.

YOu don't need either the second autonumber nor the standard
doubleprecision number - just a Long Integer foreign key/primary key
linked to the main table.

Recently I realized that the autonumber fields in the subtables don't really
do anything for me, since each subtable can have either one or no records
for each record in the main table. That is, each piece of literature has
exactly one entry in the main table and exactly one entry in exactly one of
the three subtables. So carrying over the autonumber field from the main
table is enough to uniquely identify the record. With that in mind, I
removed the autonumber field from the subtables, leaving only the copy of
the autonumber from the main table as the primary key. The relationship
window still showed the links as one-to-many and everything I've written to
date still worked.

It should be one to one: you should be linking the main table
Autonumber to the single-field long integer Primary Key of the related
table.

(Sorry to be so long-winded, but I want to make sure I explain this
properly.)

So here's the issue: When I delete such a relationship line, I can't put it
back as anything other than a one-to-one relationship. I don't want a strict
one-to-one, because for every main record, two of the three tables will have
no entry. But I'm leery of anything that smacks of witchcraft and a
relationship that can't be created without 'fooling' the table with a false
structure which is then removed again doesn't strike me as good practice.

Nothing fake about it; you're doing exactly the right thing.

Is this correct behavior for the relationship window or have I discovered
something weird? Can I set up a one-to-zero-or-one relationship somehow? I
assumed that my form would simply create one record on the many side of the
join and not address the fact that the structure could allow more than one.
Or have I made a fundamental mistake in the design somewhere?

Well... no. There will not be any records automagically created. The
relationship doesn't *create* any records; it merely prevents you from
creating invalid records.

You presumably will have three subforms, one for Books, one for
Magazines, and one for Journals.

To be squeaky clean, you may need some VBA code to ensure that you can
only create *one* child record in one of the tables (i.e. you should
not have any main table records which have both a Book and a Journal
related record). I don't know any good way to prevent this just using
the relationships window (in SQL/Server you could use a constraint,
and you might be able to do so in the latest versions of Access).

John W. Vinson[MVP]

Hello Steve,

thank you for your comments.

Perhaps I don't correctly understand the one-to-one relationship. (I've
never used it for anything.) I thought that a one-to-one meant that there
had to be exactly one record in each table on each side of the relationship.
Although, now that I think about it, that would sort of mean that you could
never add anything, since during each add, the 'other' table wouldn't yet
have the corresponding record, so the relationship would never permit any
adds.

Do you mean, then, that a one-to-one relationship can have one record on one
side and nothing on the other? Is there a no 'master' and 'slave'
arrangement like in the one-to-many? Can either side have one and the other
none?

Sorry to be so obtuse, but as I said, I've never used it for anything and
most books say it's rarely appropriate, that such things generally belong in
one table. Maybe this is one of the exceptions.

Pete


"Steve Schapel" píse v diskusním príspevku
...
Pete,

These are one-to-one relationships. Simple as that. What you have done
is entirely appropriate.

I don't quite understand your comment "I don't want a strict one-to-one,
because for every main record, two of the three tables will have no
entry." The fact that two of the three "subtables" will not contain a
record corresponding with any given main table record, is really
immaterial.

--
Steve Schapel, Microsoft Access MVP


Peter Danes wrote:
I have a strange (to me, anyway) relationship setup that I haven't found
addressed in any of the forums, online help or any of my manuals. Of
course,
maybe I'm just being dumb.

The basic structure tracks professional literature for a department's
internal library. There are three types of publications collected -
books,
magazines and journals. There is one primary table for all the data
which is
common to each type (archive number, physical location, date
acquired...)
and a separate sub-table for each of the three types, containing data
which
is unique to the type (books have an author, magazines do not...)

Originally I had a primary key in the main table using an autonumber
field
and primary keys in the subtables using a compound key of consisting of
the
autonumber from the main table as a standard doubleprecision number and
another autonumber field in the subtable. Access linked these up in
three
one-to-many relationships and everything looked great.

Recently I realized that the autonumber fields in the subtables don't
really
do anything for me, since each subtable can have either one or no
records
for each record in the main table. That is, each piece of literature has
exactly one entry in the main table and exactly one entry in exactly one
of
the three subtables. So carrying over the autonumber field from the main
table is enough to uniquely identify the record. With that in mind, I
removed the autonumber field from the subtables, leaving only the copy
of
the autonumber from the main table as the primary key. The relationship
window still showed the links as one-to-many and everything I've written
to
date still worked.

(Sorry to be so long-winded, but I want to make sure I explain this
properly.)

So here's the issue: When I delete such a relationship line, I can't put
it
back as anything other than a one-to-one relationship. I don't want a
strict
one-to-one, because for every main record, two of the three tables will
have
no entry. But I'm leery of anything that smacks of witchcraft and a
relationship that can't be created without 'fooling' the table with a
false
structure which is then removed again doesn't strike me as good
practice.

Is this correct behavior for the relationship window or have I
discovered
something weird? Can I set up a one-to-zero-or-one relationship somehow?
I
assumed that my form would simply create one record on the many side of
the
join and not address the fact that the structure could allow more than
one.
Or have I made a fundamental mistake in the design somewhere?

Pete

P.S. I'll be out of the office until the weekend, at least.

This e-mail address is fake to keep spammers and their auto-harvesters
out
of my hair. If you need to get in touch personally, I am 'pdanes' and I
use
Yahoo mail. But please use the newsgroups whenever possible, so that all
may
benefit from the exchange of ideas.

Hello John,

thank you for the explanations. It apppears that I simply had my head stuck
in the fertilizer generator again.


Well, true Subclassing - which you're using, and a classic case of it
- is somewhat tricky. You've got it almost right though.

I've seen the term subclassing here and there, but never knew what it meant
and it was always in a context that made it sound pretty involved.


Strictly speaking, the term "one to
one" is shorthand for "One to (zero or one)", since the child table
Magazines will not contain any record where the main table refers to a
Book.

Yes, and I should have figured that out myself, since a simultaneous add to
two tables is impossible. (Or is it? A join query...Still, even SQL at some
level has to put a record into one table and then into the other.)


YOu don't need either the second autonumber nor the standard
doubleprecision number - just a Long Integer foreign key/primary key
linked to the main table.

Sorry about the confusion, I did get that one right, actually. I'm using a
Czech OS and I mistranslated the data type in my original post.


Well... no. There will not be any records automagically created. The
relationship doesn't *create* any records; it merely prevents you from
creating invalid records.

I understand that - my wording there was a little awkward. Much of my
confusion came from Access leaving the lines in the relationship window as
one-to-many even after I had altered the subtables. Is that an Access
mistake? Does changing the tables and not changing the relationships leave
the DB in some indeterminate 'between' state, or does Access change the
relationship to function correctly and simply neglect to update the
relationship window? Or does it leave them displayed that way deliberately
for some reason?


You presumably will have three subforms, one for Books, one for
Magazines, and one for Journals.

Exactly. One main form with three subforms and some other doodads which are
not pertinent to this discussion. The subforms display nothing at all when
there is no record, which I find a little aggressive. My preference would be
to have the fields displayed but disabled; it seems more in line with
standard Windows functionality, but it's not a big enough issue for me to
waste time circumventing it. And at least the user will have no possible
confusion with which subform to fill out.


To be squeaky clean, you may need some VBA code to ensure that you can
only create *one* child record in one of the tables (i.e. you should
not have any main table records which have both a Book and a Journal
related record).

Yes, I have that currently under construction and no problems. That sort of
stuff is well within my capabilities.


I don't know any good way to prevent this just using
the relationships window (in SQL/Server you could use a constraint,
and you might be able to do so in the latest versions of Access).

This is a stand-alone single-user app built with A2K, but I can handle it
with VBA just fine.

I think I'll build a small test DB to experiment with to make sure I've got
it all clear. Thank you for explaining it so well.

Pete




I have a strange (to me, anyway) relationship setup that I haven't found
addressed in any of the forums, online help or any of my manuals. Of
course,
maybe I'm just being dumb.

Well, true Subclassing - which you're using, and a classic case of it
- is somewhat tricky. You've got it almost right though.

The basic structure tracks professional literature for a department's
internal library. There are three types of publications collected -
books,
magazines and journals. There is one primary table for all the data which
is
common to each type (archive number, physical location, date acquired...)
and a separate sub-table for each of the three types, containing data
which
is unique to the type (books have an author, magazines do not...)

Subclassing in its classic form: an Entity with subclasses, where all
subclasses have some attributes in common but each subclass has other
attributes unique to that class. Strictly speaking, the term "one to
one" is shorthand for "One to (zero or one)", since the child table
Magazines will not contain any record where the main table refers to a
Book.

Originally I had a primary key in the main table using an autonumber
field
and primary keys in the subtables using a compound key of consisting of
the
autonumber from the main table as a standard doubleprecision number and
another autonumber field in the subtable. Access linked these up in three
one-to-many relationships and everything looked great.

YOu don't need either the second autonumber nor the standard
doubleprecision number - just a Long Integer foreign key/primary key
linked to the main table.

Recently I realized that the autonumber fields in the subtables don't
really
do anything for me, since each subtable can have either one or no records
for each record in the main table. That is, each piece of literature has
exactly one entry in the main table and exactly one entry in exactly one
of
the three subtables. So carrying over the autonumber field from the main
table is enough to uniquely identify the record. With that in mind, I
removed the autonumber field from the subtables, leaving only the copy of
the autonumber from the main table as the primary key. The relationship
window still showed the links as one-to-many and everything I've written
to
date still worked.

It should be one to one: you should be linking the main table
Autonumber to the single-field long integer Primary Key of the related
table.

(Sorry to be so long-winded, but I want to make sure I explain this
properly.)

So here's the issue: When I delete such a relationship line, I can't put
it
back as anything other than a one-to-one relationship. I don't want a
strict
one-to-one, because for every main record, two of the three tables will
have
no entry. But I'm leery of anything that smacks of witchcraft and a
relationship that can't be created without 'fooling' the table with a
false
structure which is then removed again doesn't strike me as good practice.

Nothing fake about it; you're doing exactly the right thing.

Is this correct behavior for the relationship window or have I discovered
something weird? Can I set up a one-to-zero-or-one relationship somehow?
I
assumed that my form would simply create one record on the many side of
the
join and not address the fact that the structure could allow more than
one.
Or have I made a fundamental mistake in the design somewhere?

Well... no. There will not be any records automagically created. The
relationship doesn't *create* any records; it merely prevents you from
creating invalid records.

You presumably will have three subforms, one for Books, one for
Magazines, and one for Journals.

To be squeaky clean, you may need some VBA code to ensure that you can
only create *one* child record in one of the tables (i.e. you should
not have any main table records which have both a Book and a Journal
related record). I don't know any good way to prevent this just using
the relationships window (in SQL/Server you could use a constraint,
and you might be able to do so in the latest versions of Access).

John W. Vinson[MVP]

You've got one table with a Primary Key on it, and the other table with a
Foreign Key pointing to that first record. That means that you still need to
have a record in the first table before you can have one in the second
table: you can't have only a record in the second table.

--
Doug Steele, Microsoft Access MVP
http://I.Am/DougSteele
(no e-mails, please!)



"Peter Danes" wrote in message
...
Hello Steve,

thank you for your comments.

Perhaps I don't correctly understand the one-to-one relationship. (I've
never used it for anything.) I thought that a one-to-one meant that there
had to be exactly one record in each table on each side of the
relationship.
Although, now that I think about it, that would sort of mean that you
could
never add anything, since during each add, the 'other' table wouldn't yet
have the corresponding record, so the relationship would never permit any
adds.

Do you mean, then, that a one-to-one relationship can have one record on
one
side and nothing on the other? Is there a no 'master' and 'slave'
arrangement like in the one-to-many? Can either side have one and the
other
none?

Sorry to be so obtuse, but as I said, I've never used it for anything and
most books say it's rarely appropriate, that such things generally belong
in
one table. Maybe this is one of the exceptions.

Pete


"Steve Schapel" píse v diskusním príspevku
...
Pete,

These are one-to-one relationships. Simple as that. What you have done
is entirely appropriate.

I don't quite understand your comment "I don't want a strict one-to-one,
because for every main record, two of the three tables will have no
entry." The fact that two of the three "subtables" will not contain a
record corresponding with any given main table record, is really
immaterial.

--
Steve Schapel, Microsoft Access MVP


Peter Danes wrote:
I have a strange (to me, anyway) relationship setup that I haven't
found
addressed in any of the forums, online help or any of my manuals. Of
course,
maybe I'm just being dumb.

The basic structure tracks professional literature for a department's
internal library. There are three types of publications collected -
books,
magazines and journals. There is one primary table for all the data
which is
common to each type (archive number, physical location, date
acquired...)
and a separate sub-table for each of the three types, containing data
which
is unique to the type (books have an author, magazines do not...)

Originally I had a primary key in the main table using an autonumber
field
and primary keys in the subtables using a compound key of consisting of
the
autonumber from the main table as a standard doubleprecision number and
another autonumber field in the subtable. Access linked these up in
three
one-to-many relationships and everything looked great.

Recently I realized that the autonumber fields in the subtables don't
really
do anything for me, since each subtable can have either one or no
records
for each record in the main table. That is, each piece of literature
has
exactly one entry in the main table and exactly one entry in exactly
one
of
the three subtables. So carrying over the autonumber field from the
main
table is enough to uniquely identify the record. With that in mind, I
removed the autonumber field from the subtables, leaving only the copy
of
the autonumber from the main table as the primary key. The relationship
window still showed the links as one-to-many and everything I've
written
to
date still worked.

(Sorry to be so long-winded, but I want to make sure I explain this
properly.)

So here's the issue: When I delete such a relationship line, I can't
put
it
back as anything other than a one-to-one relationship. I don't want a
strict
one-to-one, because for every main record, two of the three tables will
have
no entry. But I'm leery of anything that smacks of witchcraft and a
relationship that can't be created without 'fooling' the table with a
false
structure which is then removed again doesn't strike me as good
practice.

Is this correct behavior for the relationship window or have I
discovered
something weird? Can I set up a one-to-zero-or-one relationship
somehow?
I
assumed that my form would simply create one record on the many side of
the
join and not address the fact that the structure could allow more than
one.
Or have I made a fundamental mistake in the design somewhere?

Pete

P.S. I'll be out of the office until the weekend, at least.

This e-mail address is fake to keep spammers and their auto-harvesters
out
of my hair. If you need to get in touch personally, I am 'pdanes' and I
use
Yahoo mail. But please use the newsgroups whenever possible, so that
all
may
benefit from the exchange of ideas.

Pete,

Yes, there is a master/slave arrangenment with defined one-to-one
relationships where Referential Integrity is enforced. If you do this
isn the Relationships window, by dragging from a field in one table to
the field in the other, the "line of command" is determined by the
direction of the drag, if you see what I mean. In your case, if you
drag from the Autonumber primary key field of the main table to the
Number (Long) field in a subtable, and then Create the relationship with
Referential Integrity enforced, well then you will be able to enter
whatever records you want in the main table, without a corresponding
record in the subtable. But you won't be allowed to enter a record in
the subtable without a related record in the main table.

If you did not enforce RI, then you would be able to enter records in
either table without a corresponding record in the other... but I can't
think of a case where this would be applicable... certainly not in your
database.

As regards "most books say it's rarely appropriate", this is true. And
the scenario you have described, sometimes called a sub-classing design,
is one of those situations where it is appropriate.

--
Steve Schapel, Microsoft Access MVP


Peter Danes wrote:
Hello Steve,

thank you for your comments.

Perhaps I don't correctly understand the one-to-one relationship. (I've
never used it for anything.) I thought that a one-to-one meant that there
had to be exactly one record in each table on each side of the relationship.
Although, now that I think about it, that would sort of mean that you could
never add anything, since during each add, the 'other' table wouldn't yet
have the corresponding record, so the relationship would never permit any
adds.

Do you mean, then, that a one-to-one relationship can have one record on one
side and nothing on the other? Is there a no 'master' and 'slave'
arrangement like in the one-to-many? Can either side have one and the other
none?

Sorry to be so obtuse, but as I said, I've never used it for anything and
most books say it's rarely appropriate, that such things generally belong in
one table. Maybe this is one of the exceptions.

Hello Steve,

You're right, I just tried it. If I drag from the main table to the sub
table, it works fine. If I drag from the sub table to the main table, it
refuses to enforce RI. What determines the direction? Is it that one has an
autonumber PK field and the other simply a long integer PK?

I just tried linking some other key fields to see what would happen and got
1-N, 1-1 and Undetermined for various combinations of fields, some key and
some not. I also tried it on two autonumber PK fields and it refused to
create the relationship at all. I only did about a dozen, not enough to get
a real sense of what is determining the result. Do you know of any websites
that go into detail on this? I have Getz, Litwin and Gilbert's 2-volume
developer's handbook, but I don't recall seeing this in there anywhere.

And the 1-1 line in the relationship window doesn't seem to give any
indication of the controlling direction once the relationship is
established. Is there some way to tell by looking, or do you have to dig
into the tabledefs and know what attributes of the fields to look for?

Pete



"Steve Schapel" píse v diskusním príspevku
...
Pete,

Yes, there is a master/slave arrangenment with defined one-to-one
relationships where Referential Integrity is enforced. If you do this
isn the Relationships window, by dragging from a field in one table to
the field in the other, the "line of command" is determined by the
direction of the drag, if you see what I mean. In your case, if you
drag from the Autonumber primary key field of the main table to the
Number (Long) field in a subtable, and then Create the relationship with
Referential Integrity enforced, well then you will be able to enter
whatever records you want in the main table, without a corresponding
record in the subtable. But you won't be allowed to enter a record in
the subtable without a related record in the main table.

If you did not enforce RI, then you would be able to enter records in
either table without a corresponding record in the other... but I can't
think of a case where this would be applicable... certainly not in your
database.

As regards "most books say it's rarely appropriate", this is true. And
the scenario you have described, sometimes called a sub-classing design,
is one of those situations where it is appropriate.

--
Steve Schapel, Microsoft Access MVP


Peter Danes wrote:
Hello Steve,

thank you for your comments.

Perhaps I don't correctly understand the one-to-one relationship. (I've
never used it for anything.) I thought that a one-to-one meant that
there
had to be exactly one record in each table on each side of the
relationship.
Although, now that I think about it, that would sort of mean that you
could
never add anything, since during each add, the 'other' table wouldn't
yet
have the corresponding record, so the relationship would never permit
any
adds.

Do you mean, then, that a one-to-one relationship can have one record on
one
side and nothing on the other? Is there a no 'master' and 'slave'
arrangement like in the one-to-many? Can either side have one and the
other
none?

Sorry to be so obtuse, but as I said, I've never used it for anything
and
most books say it's rarely appropriate, that such things generally
belong in
one table. Maybe this is one of the exceptions.

Hello Doug,

thank you for your comments. I obviously didn't understand the 1-1
relationship very well, but I think I'm starting to get a grip on it. It
seems simple on the surface, but there is more to it than I first thought.

Pete



"Douglas J. Steele" pí¹e v diskusním
pøíspìvku ...
You've got one table with a Primary Key on it, and the other table with a
Foreign Key pointing to that first record. That means that you still need
to
have a record in the first table before you can have one in the second
table: you can't have only a record in the second table.

--
Doug Steele, Microsoft Access MVP
http://I.Am/DougSteele
(no e-mails, please!)



"Peter Danes" wrote in message
...
Hello Steve,

thank you for your comments.

Perhaps I don't correctly understand the one-to-one relationship. (I've
never used it for anything.) I thought that a one-to-one meant that
there
had to be exactly one record in each table on each side of the
relationship.
Although, now that I think about it, that would sort of mean that you
could
never add anything, since during each add, the 'other' table wouldn't
yet
have the corresponding record, so the relationship would never permit
any
adds.

Do you mean, then, that a one-to-one relationship can have one record on
one
side and nothing on the other? Is there a no 'master' and 'slave'
arrangement like in the one-to-many? Can either side have one and the
other
none?

Sorry to be so obtuse, but as I said, I've never used it for anything
and
most books say it's rarely appropriate, that such things generally
belong
in
one table. Maybe this is one of the exceptions.

Pete


"Steve Schapel" píse v diskusním príspevku
...
Pete,

These are one-to-one relationships. Simple as that. What you have
done
is entirely appropriate.

I don't quite understand your comment "I don't want a strict
one-to-one,
because for every main record, two of the three tables will have no
entry." The fact that two of the three "subtables" will not contain a
record corresponding with any given main table record, is really
immaterial.

--
Steve Schapel, Microsoft Access MVP


Peter Danes wrote:
I have a strange (to me, anyway) relationship setup that I haven't
found
addressed in any of the forums, online help or any of my manuals. Of
course,
maybe I'm just being dumb.

The basic structure tracks professional literature for a department's
internal library. There are three types of publications collected -
books,
magazines and journals. There is one primary table for all the data
which is
common to each type (archive number, physical location, date
acquired...)
and a separate sub-table for each of the three types, containing data
which
is unique to the type (books have an author, magazines do not...)

Originally I had a primary key in the main table using an autonumber
field
and primary keys in the subtables using a compound key of consisting
of
the
autonumber from the main table as a standard doubleprecision number
and
another autonumber field in the subtable. Access linked these up in
three
one-to-many relationships and everything looked great.

Recently I realized that the autonumber fields in the subtables don't
really
do anything for me, since each subtable can have either one or no
records
for each record in the main table. That is, each piece of literature
has
exactly one entry in the main table and exactly one entry in exactly
one
of
the three subtables. So carrying over the autonumber field from the
main
table is enough to uniquely identify the record. With that in mind, I
removed the autonumber field from the subtables, leaving only the
copy
of
the autonumber from the main table as the primary key. The
relationship
window still showed the links as one-to-many and everything I've
written
to
date still worked.

(Sorry to be so long-winded, but I want to make sure I explain this
properly.)

So here's the issue: When I delete such a relationship line, I can't
put
it
back as anything other than a one-to-one relationship. I don't want a
strict
one-to-one, because for every main record, two of the three tables
will
have
no entry. But I'm leery of anything that smacks of witchcraft and a
relationship that can't be created without 'fooling' the table with a
false
structure which is then removed again doesn't strike me as good
practice.

Is this correct behavior for the relationship window or have I
discovered
something weird? Can I set up a one-to-zero-or-one relationship
somehow?
I
assumed that my form would simply create one record on the many side
of
the
join and not address the fact that the structure could allow more
than
one.
Or have I made a fundamental mistake in the design somewhere?

Pete

P.S. I'll be out of the office until the weekend, at least.

This e-mail address is fake to keep spammers and their
auto-harvesters
out
of my hair. If you need to get in touch personally, I am 'pdanes' and
I
use
Yahoo mail. But please use the newsgroups whenever possible, so that
all
may
benefit from the exchange of ideas.

On Tue, 6 Sep 2005 23:22:46 +0200, "Peter Danes"
wrote:

Hello John,

thank you for the explanations.

Additional comments inline...

I understand that - my wording there was a little awkward. Much of my
confusion came from Access leaving the lines in the relationship window as
one-to-many even after I had altered the subtables. Is that an Access
mistake? Does changing the tables and not changing the relationships leave
the DB in some indeterminate 'between' state, or does Access change the
relationship to function correctly and simply neglect to update the
relationship window? Or does it leave them displayed that way deliberately
for some reason?

I'd say it's just a bug, or an overlooked feature. The relationship
window doesn't get reconstructed when the table structure changes, it
seems. Whether the relationship actually changes or not is sort of
irrelevant - if there is a unique Index on the foreign key, you can't
add a second record anyhow; so regardless of what the relationship
window shows, it's effectively one to one.

You presumably will have three subforms, one for Books, one for
Magazines, and one for Journals.

Exactly. One main form with three subforms and some other doodads which are
not pertinent to this discussion. The subforms display nothing at all when
there is no record, which I find a little aggressive. My preference would be
to have the fields displayed but disabled; it seems more in line with
standard Windows functionality, but it's not a big enough issue for me to
waste time circumventing it. And at least the user will have no possible
confusion with which subform to fill out.

A blank subform sounds suspiciously like one for which the
Recordsource is not updateable. If you've changed the table structure
and the relationships, you probably need to redefine each Subform's
Recordsource to point to the (newly redesigned) tables; also check the
Master/Child Link Fields. My guess is that the form was not updated to
reflect the changed table structure.


To be squeaky clean, you may need some VBA code to ensure that you can
only create *one* child record in one of the tables (i.e. you should
not have any main table records which have both a Book and a Journal
related record).

Yes, I have that currently under construction and no problems. That sort of
stuff is well within my capabilities.

Excellent.
John W. Vinson[MVP]