Relationship feature/bug/accident

Note that ms-access "knows" if it is a one to one, or a one to "many" based
on the fact that the foreign key in the child table has a unique index
setting (if you only allow ONE value of the same type...then it has to be a
one to one. If you remove the duplicates allowed, then obviously you can
have "many").

By the way, you are correct in that the child tables do NOT need a
autonumber primary key. However, you REALLY REALLY REALLY REALLY shold put
that autonumber field in. The reason for this is if later on you do want to
relate a table to that child table, then you can without having to add that
autonumber collum. Furhter, to get the last reocrd of a child table is VERY
common question (last inoive, last order, last whatever!!). If you do NOT
add a autonumber field, then grabbing the last invoice for a custom canbe
difficlet. Adding a autonumber makes that reocrd UNIQUE, and even if you
don't relatae data to that table, you should always as a rule have a UNIQUE
way of working with a particlar reocrd. (this will bite you when you got
code, or just about anything that needs to manipulate those child reocrds.
So, a primary key is not only for reoanlships, but just for you to keep
yourself sane, and allow yo to write code etc. that can work with a SINGLE
reocrd in a table. So, don't bother to remove those autonubmer collums, as
they will come to good use in the future.

For example: If you have a autonumber, then to get the custoemrs last
inoivce you can use:

tblCustomer tblInvoice

SELECT ContactID, CompanyName , tblInvoice.InvoiceDate, tblInvoice.Pamount
FROM tblCustomer LEFT JOIN tblInvoice ON ContactID = tblInvoice.contact_id

Contact ID CompanyName InvoiceDate InvoiceAmount
1 AppleBee Jan 1/2005 $33.44
1 AppleBee Jan 1/2005 $45.00
2 Staples
3 Office Depot Dec 12/ 2005


If you look at the above, applebee has two invoices on the same day, but we
want the LAST invoice. So, we go:

SELECT ContactID, CompanyName, tblInvoice.InvoiceDate,
tblInvoice.InvoiceAmount
FROM tblCustomer LEFT JOIN tblInvoice ON tblCustomer.ContactID =
tblInvoice.contact_id
WHERE ((tblInvoice.ID)=(select top 1 id from tblInvoice where contact_id =
tblCustomer.contactID order by InvoiceDate desc, id desc)));

You will note the "top 1" to grab the LAST inoivce, but if you don't have a
autonumber, then that statement will return TWO records for the top 1.


As for which direction to draw, and how to setup relationships? It is very
important
how you do this.

I going to re-post a message of mine on this...

A left join means that a query will return the "parent" records when the
child table HAS NO correspond record.

So, if we have Customers, and Invoices tables, a left join would give us:

CustomerName InvoiceNumber
AppleBee
Donought Shop 1234
Doughnut Shop 1344

Note how AppleBee does NOT yet have a invoice number in the invoices
table..but the query still returns the record. You have to use left joins
for lookup values when you drop in many tables (can't use standard joins in
this case).

So, with a left join, the corresponding child record DOES NOT have to exist.
Just think of "left" side can exist...but the right side does NOT have to !

A middle join, or so called inner join is the standard join, and BOTH tables
have to have a value for the join. The above would produce:

CustomerName InvoiceNumber
Dounought Shop 1234
Doughutn Ship 1344

So, in the above inner join, our customer name of Applebee does not show,
since that customer does NOT yet have a invoice record in the invoice table.

To make a left join, you drop in the tables (in the query builder, or the
relationship designer), and draw the join line to the appropriate filed
between each table. You then double click on the join line. You then click
on the join type button

You get three options:

Only include rows where the joined fields from both tables are equal
(this standard default inner join)

Include ALL records from "Customers" and only those records from
"Invoices" where the joined fields are equal

(this is our left join. So, our main table Customers will be returned in
this query, REGARDLESS if the child records (invoices in this example)
exist, or not!. This is left join

Include ALL records from "Invoices" and only those records from
"Customers" where the joined fields are equal
This sis obviously a right join....

For forms, and sub-forms, and related tables, left joins are quite
important.

If you look at the following screen shot, you can see that most relations
ships are this left join, and RI is enforced.

http://www.members.shaw.ca/AlbertKal...Appendex2.html

tblBgroup (booking group) for example may, or may not have payments made
(tblPayments). Thus, you can add a booking group, and NOT have to add child
records. However, full RI is enforced, and you can see the side ways 8
"omega" sign AND THE ARROW HEAD. The simple lookup fields are simply just a
arrow drawn, and no "1", or omega sign exists (tblPayments to tblHowpaid for
example is a simple lookup). It is GREAT that I can look at the ER diagram,
and instantly know if child records are required, or they are not!!

The tables that MUST have a child records can also clearly be seen. If you
go from the tblBgroup to the its parent table, you will see table
tblBooking. You can easily see that there is a 1 to many here also, but NO
ARROW head exists. Thus, when I create a booking, my designs will ALWAYS
ASSUME that a child records in tblBgroup (booking group) will exist (ie: I
must code, and assume that when I add a tblBooking records, my code also
assumes that a tblBGroup will also have to be added). In plain English this
means that when I make a booking (reservation), my code assumes that
you MUST have people in that booking. However, I most certainly allow
people to be booked, but not yet have made any payments. So, your
relationship(s) if done right should reflect the rules you as a developer
want to maintain. I should point out that a left join, or a standard
(inner join) both allow child records to NOT exist, but you still
should correctly set this relationship, since when it comes to making
reports, and writing code...I will know what my assumptions
were at the time (ie: do I HAVE to add those child records
for the software to function correctly. So, if I write code to
make a booking, all of my code thus assumes that people
are also to be added to the booking. Break that assuming
of mine, and likely my code will break).

So, the ER diagram can convey a lot about your designs. Down the road, I can
now look at that diagram, and when writing code, I will know if the design
can, and does assume if child records are required. If you look at that
table, it is VERY RARE that I require the child record. That application has
about 60 tables, and I think only 1 or 2 in the whole thing is NOT a left
join. Hence, you most certainly should set the relation in the window for
future reference, and also it will help you when you create a query, or a
report.

Thus, if you allow a main reocrd to be added, and NOT have to have a child
reocrd (one, or many), then you should set your relsonships to be a left
join. About 80-95% of my relonaships are left joins......


--
Albert D. Kallal (Access MVP)
Edmonton, Alberta Canada

http://www.members.shaw.ca/AlbertKallal

Peter,

Peter Danes wrote:
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.

I would assume that this is simply because existing data does not comply
with the RI requirement, i.e. you already have a record in the main
table for which there is no corresponding record in the subtable.

What determines the direction? Is it that one has an
autonumber PK field and the other simply a long integer PK?

No, this shouldn't matter.

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.

If you have a unique index on a field in one table, and relating it to
another table via a field that is not unique, the relationship will be
assumed to be one-to-many. If you are relating two tables via fields
that are unique in both tables, as you are in your original examples
(they are both primary key fields, and therefore automatically unique),
the relationship will be assumed to be one-to-one.

I also tried it on two autonumber PK fields and it refused to
create the relationship at all.

No, it doesn't make sense to have a relationship based on 2 Autonumber
fields, since the value entered into the related table can not be
determined by the value entered into the primary table.

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?

If you double-click on the join line to open the relationships
properties, you will see the related fields listed in the top panel, and
this will indicate which is regarded as the main table and which the
related table.

--
Steve Schapel, Microsoft Access MVP

Hello Albert,

sorry about the delayed response, I just got back home.

Thank you for your thoughts on my problem. I'm certain that you're correct
about the need for a primary key, but if you look again at my original post,
I believe that I already have a perfectly functional one.

Every record in the main table has exactly one corresponding record in
exactly one of the subtables, and that one subrecord has as a primary key
the autonumber generated as a primary key in the main table, copied from the
main table when the subrecord is created. So each subtable contains either
nothing or exactly -one- subrecord, which has the same ID for its primary
key as the autonumber generated in the main table for the master record.
Since this ID is unique to that one subrecord, I believe that it is a valid
primary key, even though not generated as an autonumber in *that* table.

The issue of multiple subrecords and needing the last one does not arise
here. I know that requirements can change over time, but this is so specific
to the design that if something arose where I needed multiple subrecords, it
would pretty much mean a complete redesign anyway.

If you still think I am wrong and need another autonumber field, I'd be
pleased to hear more.

Pete



"Albert D.Kallal" pí¹e v diskusním pøíspìvku
...
Note that ms-access "knows" if it is a one to one, or a one to "many"
based
on the fact that the foreign key in the child table has a unique index
setting (if you only allow ONE value of the same type...then it has to be
a
one to one. If you remove the duplicates allowed, then obviously you can
have "many").

By the way, you are correct in that the child tables do NOT need a
autonumber primary key. However, you REALLY REALLY REALLY REALLY shold put
that autonumber field in. The reason for this is if later on you do want
to
relate a table to that child table, then you can without having to add
that
autonumber collum. Furhter, to get the last reocrd of a child table is
VERY
common question (last inoive, last order, last whatever!!). If you do NOT
add a autonumber field, then grabbing the last invoice for a custom canbe
difficlet. Adding a autonumber makes that reocrd UNIQUE, and even if you
don't relatae data to that table, you should always as a rule have a
UNIQUE
way of working with a particlar reocrd. (this will bite you when you got
code, or just about anything that needs to manipulate those child reocrds.
So, a primary key is not only for reoanlships, but just for you to keep
yourself sane, and allow yo to write code etc. that can work with a SINGLE
reocrd in a table. So, don't bother to remove those autonubmer collums, as
they will come to good use in the future.

For example: If you have a autonumber, then to get the custoemrs last
inoivce you can use:

tblCustomer tblInvoice

SELECT ContactID, CompanyName , tblInvoice.InvoiceDate, tblInvoice.Pamount
FROM tblCustomer LEFT JOIN tblInvoice ON ContactID = tblInvoice.contact_id

Contact ID CompanyName InvoiceDate InvoiceAmount
1 AppleBee Jan 1/2005 $33.44
1 AppleBee Jan 1/2005 $45.00
2 Staples
3 Office Depot Dec 12/ 2005


If you look at the above, applebee has two invoices on the same day, but
we
want the LAST invoice. So, we go:

SELECT ContactID, CompanyName, tblInvoice.InvoiceDate,
tblInvoice.InvoiceAmount
FROM tblCustomer LEFT JOIN tblInvoice ON tblCustomer.ContactID =
tblInvoice.contact_id
WHERE ((tblInvoice.ID)=(select top 1 id from tblInvoice where contact_id =
tblCustomer.contactID order by InvoiceDate desc, id desc)));

You will note the "top 1" to grab the LAST inoivce, but if you don't have
a
autonumber, then that statement will return TWO records for the top 1.


As for which direction to draw, and how to setup relationships? It is very
important
how you do this.

I going to re-post a message of mine on this...

A left join means that a query will return the "parent" records when the
child table HAS NO correspond record.

So, if we have Customers, and Invoices tables, a left join would give us:

CustomerName InvoiceNumber
AppleBee
Donought Shop 1234
Doughnut Shop 1344

Note how AppleBee does NOT yet have a invoice number in the invoices
table..but the query still returns the record. You have to use left joins
for lookup values when you drop in many tables (can't use standard joins
in
this case).

So, with a left join, the corresponding child record DOES NOT have to
exist.
Just think of "left" side can exist...but the right side does NOT have to
!

A middle join, or so called inner join is the standard join, and BOTH
tables
have to have a value for the join. The above would produce:

CustomerName InvoiceNumber
Dounought Shop 1234
Doughutn Ship 1344

So, in the above inner join, our customer name of Applebee does not show,
since that customer does NOT yet have a invoice record in the invoice
table.

To make a left join, you drop in the tables (in the query builder, or the
relationship designer), and draw the join line to the appropriate filed
between each table. You then double click on the join line. You then click
on the join type button

You get three options:

Only include rows where the joined fields from both tables are equal
(this standard default inner join)

Include ALL records from "Customers" and only those records from
"Invoices" where the joined fields are equal

(this is our left join. So, our main table Customers will be returned
in
this query, REGARDLESS if the child records (invoices in this example)
exist, or not!. This is left join

Include ALL records from "Invoices" and only those records from
"Customers" where the joined fields are equal
This sis obviously a right join....

For forms, and sub-forms, and related tables, left joins are quite
important.

If you look at the following screen shot, you can see that most relations
ships are this left join, and RI is enforced.

http://www.members.shaw.ca/AlbertKal...Appendex2.html

tblBgroup (booking group) for example may, or may not have payments made
(tblPayments). Thus, you can add a booking group, and NOT have to add
child
records. However, full RI is enforced, and you can see the side ways 8
"omega" sign AND THE ARROW HEAD. The simple lookup fields are simply just
a
arrow drawn, and no "1", or omega sign exists (tblPayments to tblHowpaid
for
example is a simple lookup). It is GREAT that I can look at the ER
diagram,
and instantly know if child records are required, or they are not!!

The tables that MUST have a child records can also clearly be seen. If you
go from the tblBgroup to the its parent table, you will see table
tblBooking. You can easily see that there is a 1 to many here also, but NO
ARROW head exists. Thus, when I create a booking, my designs will ALWAYS
ASSUME that a child records in tblBgroup (booking group) will exist (ie: I
must code, and assume that when I add a tblBooking records, my code also
assumes that a tblBGroup will also have to be added). In plain English
this
means that when I make a booking (reservation), my code assumes that
you MUST have people in that booking. However, I most certainly allow
people to be booked, but not yet have made any payments. So, your
relationship(s) if done right should reflect the rules you as a developer
want to maintain. I should point out that a left join, or a standard
(inner join) both allow child records to NOT exist, but you still
should correctly set this relationship, since when it comes to making
reports, and writing code...I will know what my assumptions
were at the time (ie: do I HAVE to add those child records
for the software to function correctly. So, if I write code to
make a booking, all of my code thus assumes that people
are also to be added to the booking. Break that assuming
of mine, and likely my code will break).

So, the ER diagram can convey a lot about your designs. Down the road, I
can
now look at that diagram, and when writing code, I will know if the design
can, and does assume if child records are required. If you look at that
table, it is VERY RARE that I require the child record. That application
has
about 60 tables, and I think only 1 or 2 in the whole thing is NOT a left
join. Hence, you most certainly should set the relation in the window for
future reference, and also it will help you when you create a query, or a
report.

Thus, if you allow a main reocrd to be added, and NOT have to have a child
reocrd (one, or many), then you should set your relsonships to be a left
join. About 80-95% of my relonaships are left joins......


--
Albert D. Kallal (Access MVP)
Edmonton, Alberta Canada

http://www.members.shaw.ca/AlbertKallal

Hello John,

sorry about the delayed response, I just got back home.

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.

I see. It would be nice if the window kept up with the table design, or at
least had a "Renew" button. And if you add or delete fields in a table, the
window does reflect those changes. But I guess it's a fairly minor point.


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.

No, I don't think so. I read these groups a LOT, in fact, they are my
primary source of information for troubleshooting, so you can probably guess
how often I see your name and your posts. You're one of the last people I'd
want to argue with, but this seems to be legitimate behavior. If you google
the Access archives for 'blank subform', you'll find quite a number of
references to this. And it worked that way even when I had the tables truly
one-to-many. The gist seems to be this: when a subform's recordset contains
NO records, AND the properties sheet has adding records disallowed, the
entire subform is not displayed. The subform's container on the main form
stays, but all the subform controls are invisible. It makes a certain amount
of cockeyed sense, I suppose. If there are no records to show and you can't
add any new ones, it's unlikely that you have much reason to work with the
subform. But it also blocks access to any command buttons and such that you
may want even if you have no records and no adds allowed.

I have adds disallowed because I don't want someone to make a mess, as you
pointed out could happen, by adding, for instance, both a book and journal
subrecord for one master record. The easiest way I have thought of to do
this is to not allow adds on the subforms. I create the proper subrecord in
the proper subtable based on what letter the user enters for record type
("B" book, "M" magazine, "J" journal) with all fields blank except the
primary key, requery to make the proper subform visible now that it has a
record and allow the user to finish entering data in the proper subform. To
Access, that is then updating an existing record, but to the user, it
appears to be a seamless continuation of the data entry process.

Pete



"John Vinson" píse v diskusním
príspevku ...
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]

"Albert D.Kallal" wrote in
:

I hate to disagree with a MVP, Albert, but may I chip in a little bit here?

By the way, you are correct in that the child tables do NOT need a
autonumber primary key.

As I understand it, the set up is something like

Publications (*PubsID, ArchiveNum, PhysLocation, etc)

Books(*PubsID, Author, House, etc)
FK (PubsID) references Publications

Magazines(*PubsID, IssuesPerYear, etc)
FK (PubsID) references Publications


However, you REALLY REALLY REALLY REALLY shold
put that autonumber field in.

In other words,
Books(*BookNum, PubsID, Author, House, etc)
Unique (PubsID)
FK (PubsID) references Publications

Magazines(*MagazineNumber, PubsID, IssuesPerYear, etc)
Unique (PubsID)
FK (PubsID) references Publications


The reason for this is if later on you
do want to relate a table to that child table, then you can without
having to add that autonumber collum.

Surely, this is just the best reason for _not_ adding another ID column.
Say there was a need for another table of Translations for local
translations of magazines: you would presumably suggest a design like

Translations(*MagazineNumber, *LanguageCode, etc)
FK MagazineNumber references Magazines

(leaving aside for a minute the question of having a PK *TranslationID...),
while I would suggest something like this:

Translations(*PubsID, *LanguageCode, etc)
FK PubsID references Magazines

(note that the relationship is constrained to the Magazines table, not the
publications table)

Now, say there is a need for a query of PhysicalLocations for each
translation: the first variation would need a three-table join on
Translations - Magazines - Publications, while the second would need only
Translations and Publications. It's not an uncommon scenario, and in my
view, relegates the Magazines table to an (unneccesary) mapping function.

If you look at the above, applebee has two invoices on the
same day, but we want the LAST invoice. So, we go:

You don't really use autonumbers to provide a MostRecent function do you? I
refer to the (I think) seventh commandment:

Thou shalt not use Autonumber if the field is meant to have
meaning for thy users.

If you want to know the most recent invoice in one day, then you need a
time field to sort by. No?

Best wishes


Tim F

You don't really use autonumbers to provide a MostRecent function do you?
I
refer to the (I think) seventh commandment:

Thou shalt not use Autonumber if the field is meant to have
meaning for thy users.

If you want to know the most recent invoice in one day, then you need a
time field to sort by. No?

An excellent answer. And, yes, a timestamp is a good solution.

However, one should point out that simply using a autonumber for order STILL
does not give the actual numbers any meaning to the user. For example, we
can state that we are going to use autonumbers for a relation, but that does
not mean we spilled the beans, and told the user what we are using the
autonumber for!!! In other words, breaking silence in that we are using a
autonumber for something such as relationships does NOT break that above
rule. So, we can tell a user that we are going to use autonumbers for
relations, and that most certainly has a meaning to the user!!. So, the
above concept being explain is that the user never sees, or assigns an
actual meaning to a ACTUAL autonumber. So, using the autonumber for setting
the order no more breaks the above rule then using them for relationships.
As long as the user never writes down, or sees those autonubmers, .you are
free to use them as you see fit (to build relationships, or to set
order ---- and telling the user we are going to do this would not all of a
sudden mean we can't use the auotnumber for a relationship!). To split
hairs, the concept of "order" could be argued to have more meaning then the
concept of a relationship. (the reason being that a autonumber can be a
random number for relationships, but using the autonuber for order implies a
increasing value all the time, so your debate is somewhat correct in that
order does have meaning, but so does the concept of a relationship, but not
as much meaning!!).

As mentioned, a timestamp is a very good solution to retrieve the last
record However, it still needs pointing out that another good rule in
database designs is to ensure that ALL records have a unique identifier, or
a primary key. This concept of having a PK is NOT ONLY for building
relationships, but also to uniquely identify a record in a table. If we
start discussion about databases, and codds rules, you will find that these
scholars will quickly point out that a record in a table needs a Primary key
to allow one to accomplish a normalized database. (but, lets not even go
there..and again, a shrewd person could point out that a compound key
consisting of the foreign key + the timestamp could produce a primary key).

However, my point is that just because a child table don't seem to need a
primary key, adding one gives you the ability to restive the last record. It
gives you the ability to uniquely identify a record. And, for future
designs, and modifications, a new child table can be added, and you will
never have to worry if the parent table is missing a PK.

So, adding a timestamp might solve some of these problems, but consideration
needs to be given for other issues

So, I think it is just a bonus to always have a PK. And, of couse, this is
not a "rule", but simply a good suggeston on my part....

--
Albert D. Kallal (Access MVP)
Edmonton, Alberta Canada

http://www.members.shaw.ca/AlbertKallal

"Albert D.Kallal" wrote in
:


If you want to know the most recent invoice in one day, then you need
a time field to sort by. No?

An excellent answer. And, yes, a timestamp is a good solution.

However, one should point out that simply using a autonumber for order
STILL does not give the actual numbers any meaning to the user.

Fair point indeed: I'm still a bit unhappy about requiring autonumbers to
be always in order... although they are called incrementing, there are
too many situations when an AN will be created out of sequence... The
point at which the number is allocated will be (a) significant and (b)
probably provider-specific. For example:

Joe is having a lot of trouble making up his mind about his new laser
printer: the salesman has opened the invoice and is waiting to enter the
HP LaserCannon 34009 with 5000 page sheet feed. Meanwhile, Joe's
secretary nudges him and points to the empty cupboard, so he asks for
some copier paper to be put on a new order (and a new invoice) for same
day messenger deliver. Finally, he makes up his mind and goes for the
BudgetBrick MagicWriter with the built in WAN and cofee-maker.

Which invoice is the most recent? Local business rules may legitimately
choose either one, but it certainly should not be an accidental side
effect of SQL Server versus Jet architecture!

However, my point is that just because a child table don't seem to
need a primary key, adding one gives you the ability to restive the
last record. It gives you the ability to uniquely identify a record.

I would sooner stick needles in my eyes than recommend a table without a
primary key: my point about the subclassing/ one-to-one relationship
described was that the FK _is_ the PK (that should be the other way
round) and there is no need to specify a new one. I am now way too old to
get into arguments about substitute PKs in heirarchies of one-to-many
relations; but this is a somewhat different thing.

All the best


Tim F

"Tim Ferguson" wrote in message


I would sooner stick needles in my eyes than recommend a table
without a primary key: my point about the subclassing/ one-to-one
relationship described was that the FK _is_ the PK (that should be
the other way round) and there is no need to specify a new one. I am
now way too old to get into arguments about substitute PKs in
heirarchies of one-to-many relations; but this is a somewhat
different thing.

I'm with Tim on this one.

--
Dirk Goldgar, MS Access MVP
www.datagnostics.com

(please reply to the newsgroup)

"Dirk Goldgar" wrote in message
...
"Tim Ferguson" wrote in message


I would sooner stick needles in my eyes than recommend a table
without a primary key: my point about the subclassing/ one-to-one
relationship

Ah, ok...if we are talking about a one to one relationship, then of course I
agree!!
(I kind of missed that point).

To be fair, my response was NOT in the context of a one to one relationship,
but a one to many.

For a one to one, without question we already have a PK in the child table
(that comes from the parent table), and thus one would NOT want a autonumber
pk for the child table......

(this issue was so obvious, that I did not even think it was being debated
!!!);.

If I wrongly suggested that one should put a autonumber in a child table
that is NOT the primary key, then my apologies, as I would not suggest that
for one second. (and,in re-reading my post, I mentioned autonumber, but NOT
pk - that is was not my intention!).

Since the original question is in this context, then I am guilty of
confusing here. However, I can assure you that my point is that you want a
PK in the table...and if you already got one...then adding a autonumber to
that table is just not a good idea at all!

In reading this, we all actually seem to be on the same channel, but I was
not 100% clear here. So, no, I would NOT suggest adding a autonmber field
UNLESS it is the PK (and, in the original post....the child table already
has a PK)......

I now see why you and Tim pointed this out, as it looked like I was
suggesting to add a autonumber field that was NOT the PK.....

So, great heads up here folks...and thanks!!!

--
Albert D. Kallal (Access MVP)
Edmonton, Alberta Canada

http://www.members.shaw.ca/AlbertKallal

"Albert D.Kallal" wrote in
:


In reading this, we all actually seem to be on the same channel, but I
was not 100% clear here. So, no, I would NOT suggest adding a
autonmber field UNLESS it is the PK (and, in the original post....the
child table already has a PK)......



That makes us all in violent agreement then!

All the best

Tim F