Linking Tables error

[Guest]

I am new to access and getting an error message

"No unique index found for the referenced field of the primary table."

when I try to join a new table in relationships. Don't understand the message.

Join to table Join from table
tblRefPhyContact tblRefPhyContactNotes

These are the Primary Keys for each, and the unique index.
RefPhyID RefPhyID
ContactID ContactID
NotesID

I do not understand.

 

[John Vinson]

I am new to access and getting an error message

"No unique index found for the referenced field of the primary table."

when I try to join a new table in relationships. Don't understand the message.

Join to table Join from table
tblRefPhyContact tblRefPhyContactNotes

These are the Primary Keys for each, and the unique index.
RefPhyID RefPhyID
ContactID ContactID
NotesID

I do not understand.

It sounds like you should be joining RefPhyID to RefPhyID, and
ContactID to ContactID. Are you perhaps joining just one of these
fields?

John W. Vinson[MVP]

 

[Guest]

Yes I tried joining both RefPhy and Contacts, but received the same message.
I added a NotesID in the Primary table, i then joined all 3,(RefPhy,
Contacts, & NotesID) and the link was accepted. I cannot attach a smapshot of
the relationship, but a snapshot would show you what i am attempting to
setup.

Thanks for the Help.

Francis

 

[John Vinson]

I am new to access and getting an error message

"No unique index found for the referenced field of the primary table."

when I try to join a new table in relationships. Don't understand the message.

Join to table Join from table
tblRefPhyContact tblRefPhyContactNotes

These are the Primary Keys for each, and the unique index.
RefPhyID RefPhyID
ContactID ContactID
NotesID

I do not understand.

I fear you're still going to have either redundant fields or erroneous
joins. Some specific questions:

What is the Primery Key of each table? Is it one field, or are there
two fields with a key icon in table design view?

In the Join, which field is joined to which? (Just tell me fieldnames,
I don't think a picture is needed).

What real-life entity is represented by each table?

What is the real-life meaning of each field - what Attribute of the
entity does RefPhyID represent, for example?

John W. Vinson[MVP]

 

[Guest]

This is a medical Database.
RefPhy is a referring physician
Contact is the main contact at the physician's office.

Table 1 - tblRefPhy table has one pri RefPhyID. (AutoNumber)
Table 3 - tblContact table has one Pri ContactID.(AutoNumber)
(many to many relationship) so i created Table 2 - tblRefPhyContact table
has RefPhyID & ContactID as pri key.(Number for both data type) That works
fine.
Table1 RefPhyID joined to Table 2 RefPhyID.
Table 2 ContactID joined to Table 3 ContactID.

I need to log notes from the contacts and the date of the notes. There will
be more than one note for every contact.
Table 5 - tblContactNotes has NotesID has Pri. (AutoNumber)
Table 4 - tblRefPhyContactNotes has RefPhyID, ContactID, & NotesID as
pri.(All Number for all data type)
Table 4 NotesID joined to Table 5 NotesID. (I have a date field in Table 4
and a notes field in Table 5. I do not see any redundant field names, just
the basic last name, first name, address .....)

Problem:
I am trying to join
Table 2 RefPhyID & ContactID to Table 4 RefPhyId & ContactsID.(Enforce
Referential Integrity & Cascade Updated Related Fields(Same as other linkes))
I receive an error - "No Unique index found for the referenced field of the
primary table."
I hope this makes sense.
Appreciate the help.

 

[John Vinson]

I am trying to join
Table 2 RefPhyID & ContactID to Table 4 RefPhyId & ContactsID.(Enforce
Referential Integrity & Cascade Updated Related Fields(Same as other linkes))
I receive an error - "No Unique index found for the referenced field of the
primary table."

For one thing, I'd remove all the Cascade Updates. They apply only
when the Primary Key value is edited in the "one" side table; and
since you can't edit Autonumbers, that should never occur.

Doublecheck that both RefPhyID and ContactID have the "key" icon by
them in Table2. This *should* work if those two fields are in fact the
joint primary key. You can also check the indexes - open Table2 in
design view and click the indexes icon (lightning bolt hitting a
datasheet); is there in fact a unique index (probably named Primary
Key, though any name should work) on those two fields?

John W. Vinson[MVP]

 

[aaron.kempf]

linked tables are obsolete.

use Access Data Projects and keep all your data in one place.

Spit on anyone that uses MDB for ANYTHING

-Aaron

 

[Guest]

Everything is as you suggested. i started a new database, with the same
fields, and it works. I must have something embedded in the old database,
cause the new one works as you suggested.

Thanks for the help.