Create Relationship--"No unique index found for blah blah.."?

[TroyMcClure]

I'm losing my mind.
I have 2 tables--TicketHeader and TicketDetail.
Header PK key is:
OrdDate
OrdNum
TktNum

Detail PK key is:
OrdDate
OrdNum
TktNum
TktLine

I'm trying to create one-many as shown:
[tt]
Header Detail
OrdDate---OrdDate
OrdNum----OrdNum
TktNum----TktNum
[/tt]
But I get the error:
"No unique index found for the referenced field in the Primary table"

I've double checked the Indexes, the keys shown are valid, and even in the Relationship dialog the 3 Header fields are in bold. There are no orphaned detail records or even any header without details. The Relationship Type says 'One to Many'.

I do have an alternate key 'RecordID', an Autonumber, but this is NOT the PK, it's index is simply Unique. I even tried setting the Unique to 'No' for this index, and still no change.

What could be wrong--have I missed something painfully obvious?
Thanks,
=Troy

 

[dhookom]

Why would you not set the RecordID field to the primary key and add a long integer field in the detail table for the foreign key? You can leave the three fields as unique.

Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]

 

[TroyMcClure]

dhookum,
I steer clear of using Autonumber as a PK...I know that's a contentious issue here...
However one key reason is that it's much easier visually for a developer to look at the Relationship window and know the 'real' relationship--the actual business process relationship--rather than just see a couple of generic recordID's linked that mean nothing except to JET.

Anyway, can you think of anything I may have missed in setting this relationship up? I mean, this is the first time I've seen this error when everything has seemed to be correct. Any known bugs? KB articles were too numerous to sort through when googling, so I thought a fresh set of eyes might see something glaring that I'd missed.
=Troy

 

[willir]

In line with Duane's advice...

tblTicketHeader
TicketID - primary key, autonumber(?)
OrderDate
EventName
...etc

tblTicketDetail
TicketDetailID - primary key, autonumber(?)
TicketID - foreign key to tblTicketHeader.TicketID
TicketLine
ItemComment
...etc

Much simplier this way to use the TicketID (or TicketNumber) as a unique field that links the two tables.

Now...
I am assuming you have a one-to-many relationship. One ticket has one or more ticket detail items.
Your relationships are "normalized"

You also run into problems when creating relationships if your table already has data in them. For example, missing data or duplicate data or one data type not matching the linked data.

Perhaps you may want to work with empty tables to ensure everything is working. You can then use an append query to transfer any data from the table with data to the new table. Access will not update tables where the data is wrong, or integrity rules are broken -- this is a good thing.

Richard