Cant Enforce referential Integrity

[Randy]

Access 2000. I have a main table and a details table. I have 2 PK joined
in the main table to prevent duplicates at the main office. The problem is
I can not enforce referential integrity and allow cascade deletes with this
setup ( 2 PK Joined, must have unique identifier etc.) Is this right? Or
am I missing something?...Thanks

 

[John Vinson]

Access 2000. I have a main table and a details table. I have 2 PK joined
in the main table to prevent duplicates at the main office. The problem is
I can not enforce referential integrity and allow cascade deletes with this
setup ( 2 PK Joined, must have unique identifier etc.) Is this right? Or
am I missing something?...Thanks

A Table can only have one PK (though this may consist of one, two, or even
ten fields). I presume that you have a two-field PK in your main table, and
that you're joining each of these fields to its corresponding foreign key
field in the details table? If so you need to specify the referential
integrity on each join line.

What in fact is the structure (PKs, FKs) of the tables involved, and how are
you trying to enforce the join?

 

[Randy]

My main table "Account" has two PK joined together, they are "AccountID" and
"DistrictID". AccountID is an autonumber and DistrictID is number. The
details table "Accountsubform" also has the fields of "AccountID" and
"DistrictID" I have a one to one relationship via "AccountID" I have no PK
in my "Accountsubform" table. My Form "Accounts" has a subform linked to
AccountID and DistrictID...Does this help...Thanks

 

[Ken Snell \(MVP\)]

Another way to establish a multi-field relationship is to use Ctrl+Click to
select multiple fields in the parent table, then drag the highlighted fields
onto the child table -- the relationship window will open and allow you to
select the matching fields in the child table and to set referential
integrity for the multiple joins.

 

[John Vinson]

My main table "Account" has two PK joined together, they are "AccountID" and
"DistrictID". AccountID is an autonumber and DistrictID is number. The
details table "Accountsubform" also has the fields of "AccountID" and
"DistrictID" I have a one to one relationship via "AccountID" I have no PK
in my "Accountsubform" table. My Form "Accounts" has a subform linked to
AccountID and DistrictID...Does this help...Thanks

This is a bit odd. An Autonumber is unique by design already, so having an
autonumber as one field of a two-field PK is sort of pointless: having a
second field doesn't make it any MORE unique!

And if you have a one to one relationship, it suggests that AccountID is
unique in the second table (making it functionally a primary key even if it
doesn't have the key icon). What is the datatype in the second table?

The form/subform relationship is sort of irrelevant at this point: it is
affected by the table relationships, but it does not affect the table
relationships.

 

[Randy]

I still cant enforce referential integrity after your suggestion. I now
have two PK joined ("AccountID" and 'District ID") In both my main table and
my details table. I keep getting "No unique index found"..Thanks

 

[Ken Snell \(MVP\)]

Let's start at the beginning.

Tell us the structure of the two tables in question. Identify the fields,
data types, indices, and the primary keys.

 

[Randy]

Table 1: named "Account" with 2 joined PK fileds of "AccountID" which is
set to autonumber and "DistrictID" set to number, other fields "EID" set to
number, "CurrentDate" set to date, "Debits" set to currency, "Credits" set
to currency, "AppID" set to number.
Table 2: Named "Accountsubform" with 2 joined PK of "AccountID" which is
set to number, "DistrictID"set to number. Other fields of "Miles" set to
number, "hours" set to number, "Fees" set to currency.
I am trying to establish a relationship of referential integrity and cascade
deletes. Doesn't work, states no unique index.
Thanks...Randy

 

[Ken Snell \(MVP\)]

You're using the same PK in both tables. That setup cannot have referential
integrity, as any changes might be invalid for ensuring a unique index in
the second table.

 

[Ken Snell \(MVP\)]

Correction... I just set up a test situation and was able to set up
two-field relationship as you describe with referential integrity and with
cascase delete. (I apologize for the wrong info.)

Describe how you're trying to establish the relationships. Are you selecting
the fields in Account and dragging onto Accountsubform (in Relationships
window)? Are you selecting the fields in Accountsubform to match to the
fields in Account?

 

[Randy]

Yes , I am selecting the fields in account and dragging them to the same
fields in accountsubform...and then trying to select referential
integrity...

 

[Randy]

I figured it out, thanks for the help..Randy

Yes , I am selecting the fields in account and dragging them to the same
fields in accountsubform...and then trying to select referential
integrity...