Dual Primary key acting like single primary key

Hi all

I am having a problem with my data base relationship. the problem is if I have a two records with the same ISBN but different customer than it will not accept it. (See Attachment). It would be understandable if both had same ISBN and customer number, but that is not how I want it set up.

The tables in question is "tbl_Sales_Book_Description_Current" and "tbl_Exchange_Book_Description_Current"

Use ISBN number:
0-07-13-5241-4

and customer_No uses a number system (1, 2 and 3) formed by a variable which is changed by buttons in form.

With "tbl_Exchange_Book_Description_Current" I still need to re-make relationship, but did not work before, and because I have renamed my tables, the name is too long to use in relationship. How do I stop access from doing this?

I know I can get around it by creating a table with a small name, create the relationship, and rename to longer version.

This is the backend of my database.

Thanks in advance

I can't download from the forum because of my site's security policy. So I can't see your design. But some of what you have said tells me the problem. It is a design flaw having to do with normalization issues.

You can't have two primary keys in a single table. Period. If you have two items with the same ISBN, then the ISBN is not a candidate key for primary status in that table (at least by itself, it isn't.)

You need an ISBN table where there IS NO DUPLICATION. One record for each distinct ISBN. End of story.

Then, you need a customer table. One record for each distinct customer. Again, no duplication. No exceptions.

Now, you need a junction table. In this table, you have two fields - the ISBN and customer number. In this junction table, neither field needs to be unique - and probably cannot be unique. The COMBINATION of two fields could be used to form a unique compound key. And HERE is the only place where you can have duplicate ISBNs or duplicate Customer entries.

Thanks Doc, took your advice and works now. thanks again