2 tables with referential integrity

If you choose a one-to-many relationship and select the join property
"Include ALL records from Contacts and only those records from Orgs where the
joined fields are equal", wouldn't that allow you to have records in Contacts
without corresponding records in Orgs? Are you saying that if you choose
"Only include records where the joined fields in both tables are equal", but
there are null entries in the joined field in Contacts, you can avoid having
a related record in Orgs?

And I have a general "newbie" question: If one doesn't need to enforce
referential integrity, is there any benefit to creating these table
relationships?

"Jeff Boyce" wrote:

Jacquie

I believe that if you tell Access to enforce referential integrity between
those two, you're telling it that you cannot have a Contact record without a
corresponding Org record.

Regards

Jeff Boyce
Microsoft Office/Access MVP

"Jacquie" wrote in message
...
I have 2 tables with referential integrity: contacts and orgs. it's a
many-to-one, with many contacts connected to one org. however, I also have
some contacts with no org, but when I try to add them, access tells me I
have
to have an org to proceed. BUT THERE IS NO ORG!! org is not required in
the
contacts table. what do I have to do? Thanks!!

Hi Angela

As far as I know, changing the join property to "Include ALL the records
from..." has no effect whatsoever on the actual relationship or on
relational integrity. It simply means that whenever you add those two
tables to a query in design view, the join is created, by default, as an
OUTER join (with an arrow on one end), not as an INNER join.

As I said in another post, RI does not prevent you having a null value in
the foreign key of the many-side table, no matter what the join type.

And for your "newbie" question, I believe you would gain some advantage in
speed of queries with a non-RI relationship, but I can't confirm that for
certain. Personally, I find that relationships where RI is not appropriate
are *extremely* rare.

--
Good Luck :-)

Graham Mandeno [Access MVP]
Auckland, New Zealand

"Angela" wrote in message
...
If you choose a one-to-many relationship and select the join property
"Include ALL records from Contacts and only those records from Orgs where
the
joined fields are equal", wouldn't that allow you to have records in
Contacts
without corresponding records in Orgs? Are you saying that if you choose
"Only include records where the joined fields in both tables are equal",
but
there are null entries in the joined field in Contacts, you can avoid
having
a related record in Orgs?

And I have a general "newbie" question: If one doesn't need to enforce
referential integrity, is there any benefit to creating these table
relationships?

"Jeff Boyce" wrote:

Jacquie

I believe that if you tell Access to enforce referential integrity
between
those two, you're telling it that you cannot have a Contact record
without a
corresponding Org record.

Regards

Jeff Boyce
Microsoft Office/Access MVP

"Jacquie" wrote in message
...
I have 2 tables with referential integrity: contacts and orgs. it's a
many-to-one, with many contacts connected to one org. however, I also
have
some contacts with no org, but when I try to add them, access tells me
I
have
to have an org to proceed. BUT THERE IS NO ORG!! org is not required in
the
contacts table. what do I have to do? Thanks!!

About once a month I get an intelligent thought and that just happenned to be
the day. My thinking originally got derailed by accepting the "can't add
records due to referential integrity" premise as categorically true.