I am developing a database for a company that sells products on ebay at commission rates for clients. When a client is acquired by the company, they are assigned a unique 3-digit client number. Each item that they sell is assigned a unique 3-4 digit number. When joined together, like below, the client ID and item ID create a unique item number.


and therefore cannot be duplicated.

I have a <b>tblClients.ClientID (P)</b> field, which links to <b>tblItems.ClientID</b>, which is not a primary key. This relationship is one client, many items.

I then have <b>tblItems.ItemID (P)</b>, which links to <b>tblListings.ItemID</b>. The latter is not a primary key. This relationship is one item, many listings (because some items are relisted).

My dilemma is this. Because multiple clients can have the same item number (e.g Client A, B and C can and will all have items 001, 002, 003 and so forth), I need to allow duplicates of item numbers. BUT because Client A cannot have more than one instance of item number 001, I need to prevent duplicate combinations of client IDs and item IDs.

I went back and altered <b>tblItems.ClientID</b> to a primary key, which I believed would solve the problem. To edit the primary key, I had to delete the existing relationships between all three tables, and put it back together. So <b>tblClients.ClientID (P)</b> and <b>tblItems.ClientID (P)</b> were linked back in a one-to-many relationship; but when I tried to put back the one-to-many relationship between <b>tblItems.ItemID (P)</b> and <b>tblListings.ItemID</b>, it told me the relationship was indeterminate! When I converted <b>tblItems.ClientI</b> back to non-primary key, the indeterminate problem went away.

If somebody could please give me some advice about this, I would be so grateful!