Trouble with Composite Keys

[MakinBacon91]

Hello all,

I am having trouble with making a composite key.
Here is a picture with my table, the error, and table design view below: Pasteboard — Uploaded Image

I have a table with all of the Customer Abbreviations, that is a primary key for that table.

In the Job info table, Customer Abbreviations will not be unique, and Job number will not be unique,
But the combination of Customer Abbreviation with Job number will be unique.

Any thoughts on how I can achieve this type of data structure?

Thanks,
Bacon

 

[MakinBacon91]

Hello all,

I am having trouble with making a composite key.
Here is a picture with my table, the error, and table design view below: Pasteboard — Uploaded Image

I have a table with all of the Customer Abbreviations, that is a primary key for that table.

In the Job info table, Customer Abbreviations will not be unique, and Job number will not be unique,
But the combination of Customer Abbreviation with Job number will be unique.

Any thoughts on how I can achieve this type of data structure?

Thanks,
Bacon

I have been doing some research, to no avail yet, but I thought I would mention I am also looking into using a Multi-Field Unique Index.

Edit:
Also, I think I found one of my problems.
The table with Customer Abbreviations is linked 1-1 with my Job Info Table.

 

[MakinBacon91]

Haha,

Yeah, I figured this one out!!
I just had to go in to the Relationships display, delete the relationship between Customer Table and Job Info Table, then recreate the link checking the "Enforce Referential Integrity" box.

Thanks guys!

Edit: I used a Multi-Field Unique Index, instead of a primary key.

 

[HiTechCoach]

Haha,

Yeah, I figured this one out!!
I just had to go in to the Relationships display, delete the relationship between Customer Table and Job Info Table, then recreate the link checking the "Enforce Referential Integrity" box.

Thanks guys!

Edit: I used a Multi-Field Unique Index, instead of a primary key.

Thanks for sharing your solution.

I used a Multi-Field Unique Index, instead of a primary key.

I always do that. Stick with that as you will have a lot less headaches. I have never seen the requirement that had to use composite (multi-field) primary key in 30+ years with 1000+ database. Not a single PC database like access Access to client sever database like SQL Servers, and even mainframe database. I always been able to use anything but a single field, system assigned, primary key. I do create an index with multiple fields.