Multi-field primary keys question

[Terry Lowe]

Hi. I used to think I understood databases but apparently not as well as I
thought... I need a bit of help from the gurus please on this project I am
writing.

Here's the deal (simplified): 4 tables:

1) Client Table: PK=ClientID
2) Employee Table: PK=EmployeeID
3) Assignment Table: PK=ClientID and AssignmentID (because each assignment
can belong to multiple Clients)
4) Assignment/Employee Table: PK=ClientID, AssignmentID, EmployeeID (because
each employee for an assignment is specific to a client).

I have the relationships set up and all of the links work fine except
Assignment/Employee Table. When entering data in table view (datasheet), the
combo box for AssignmentID shows ALL assignments - not just those assigned to
that ClientID via the Assignment Table. I have tried populating the list from
the table directly and with a filtered query to no avail.

I hope I have explained this well enough. Any help would be greatly
appreciated. Thanks.

 

[Ken Snell [MVP]]

Sounds as if you're using "lookup fields" in the table directly. To do what
you seek, create a form for the data entry. The combo box on that form can
be filtered to show just the assignments for a specific client.

By the way, "lookup fields" in a table are not well thought of in these
parts.... < g >
http://www.mvps.org/access/lookupfields.htm

 

[Pat Hartman]

You seem to be missing a table. There should be an Assignment table with
the primary key of Assignment and your present Assignment table is really
the relation table between client and assignment and should more properly be
named ClientAssignment table.

 

[Terry Lowe]

Thanks for the reply Pat. I was kind of thinking that and tried it but since
I am "grouping" assignments by ClientID it didn't seem to work either. Can
you tell me what is the difference between having a separate table with the
key of ClientID and AssignmentID and having the table like it is now with
ClientID, AssignmentID and EmployeeID? Can Access not handle a table with
more than 2 fields in a key?

Also, the way I have it may be more correct than I thought just that after
reading Ken's post, I may be expecting more from the combo boxes in datasheet
view than I should be. It sounds that what I have may work if I use a form
instead of a datasheet. What do you think?