Referential Integrity

[Sandra]

I have a master table called PeoplePlaces. I have a
table called Relations which lists individuals related to
a record in PeoplePlaces. I have 2 tables, RAffiliations
and RCampaigns that list specific information about the
individuals in Relations. I want to establish
Referential Integrity with all these tables so that if I
delete a record from PeoplePlaces all the linked records
in Relations, RAffiliations and RCampaigns are also
deleted. Or, if I delete a record from Relations, it
will also delete the related records in RAffiliations and
RCampaigns. I have no problem establishing Referential
Integrity between PeoplePlaces and Relations, but I
cannot seem to accomplish this between Relations and
RAffiliations and RCampaigns so that all the records will
cascade properly.

I get an error message about "...records relating to an
employee in the related table, but no record for the
employee in the primary table..."

I don't understand the problem. And even if I delete the
link between PeoplePlaces and Relations so that Relations
is a stand-alone table, I still cannot get the other 2
tables to establish Referential Integrity. How do I fix
this???

TIA,
Sandra

 

[Jeff Boyce]

Sandra

From the error message you quote, it sounds like your RAffiliations and/or
RCampaigns tables have foreign keys that aren't in the Relations table.

Have you run the "unmatched query wizard" on the "child" (RAffiliations) vs.
"parent" (Relations) tables, to find out which IDs RAffiliations holds that
aren't in Relations? And then another to check on RCampaigns vs. Relations?

Good luck

Jeff Boyce
<Access MVP>

 

[Sandra]

Hmmmm....I don't have a "unmatched query wizard" in my
Access, nor does it appear to be available as an Add-In
through Add-In Manager. As to the tables, they are all
empty (except when I am testing) as I am still in the
process of developing this database. Here is the
structure of the 3 tables:

RELATIONS
RID-PK autonumber
RPPID-Link to record PPID in master PEOPLEPLACES
RPrefix
RFirstName
Yadda, yadda, yadda...

RAFFILIATIONS
RMHCID-PK autonumber
ARID-Link to record RID in RELATIONS
RBoard
RStaff
RVolunteer
Yadda, yadda, yadda...

RCAMPAIGNS
CpnID-PK autonumber
CRID-Link to record RID in RELATIONS
RAnnualGiving
RWildGameFeast
RGolfTournament
RWorldChildrensDay
Yadda, yadda, yadda...

As I type this it occurs to me that there is a one-to-one
relationship between RELATIONS and the other 2 tables, I
should probably just plunk all the fields (checkboxes)
into the RELATIONS table and save myself a bundle of
queries LOL!

What do you think?

Sandra

 

[Jeff Boyce]

Sandra

It would be unusual if your copy of Access doesn't have the query wizard.
If you go to the database window, click on the drop-down toolbar button for
new reports, queries, forms, etc., and select "unmatched" from the list,
that's the "wizard".

You didn't say if the problem only occurs when the tables are empty, or when
"you are testing" (and, I assume, you've put data in). Create and run the
unmatched query after you have data in the tables.

When I looked over the table descriptions you posted, I noticed that you
used "repeating fields" to categorize. For example, your Campaign table has
what appears to be one field for each campaign (AnnualGiving,
GolfTournament, ...), and your Affiliation table has Board, Staff,
Volunteer, ... fields, describing roles. This type of design is typical for
a spreadsheet, but counterproductive in a relational database.

Step away from the computer, m'am (<g>). Before you go any further, I'd
suggest using paper/pencil to revisit the normalization of your data. In
addition to Access HELP on the topic, check out:

http://support.microsoft.com

for KB# 100139.

Good luck

Jeff Boyce
<Access MVP>