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.
545.001 ....
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.ClientID</b> back to non-
primary key, the indeterminate problem went away.
If somebody could please give me some advice about this, I would be
really grateful!
Yahoo! Groups Links
<*> To visit your group on the web, go to:
http://groups.yahoo.com/group/ms_access/
<*> To unsubscribe from this group, send an email to:
[EMAIL PROTECTED]
<*> Your use of Yahoo! Groups is subject to:
http://docs.yahoo.com/info/terms/
