Cannot create one to many relationship

[TonyB]

I have a simple test design with two tables, each with PK, and one has a FK.
I am trying to create a relationship from the PK of one table to the FK of
another table. But access always makes it a 1-1 not 1-many ? What might be
the explanation of this ?
Example
tblStaff tblBonus
Initials (PK) Date (PK)
FamilyName Initials (FK)
ChristianName Amount
Reason
If I create a relationship between the tblBonus Initials(FK) to tblStaff
Initials(PK) it is 1 - 1 ?
Initials is a two character field

Thanks Tony

 

[Joan Wild]

Likely because Initials in the tblBonus table has a unique index on it.

I'm not so sure that using Initials is such a good idea for the PK of
tblStaff. It needs to be unique and you are likely to soon run into two
staff with the same initials.

 

[TonyB]

Hi Joan,
Yes it was that. I hadn't noticed indexing was enabled for the FK.
Thanks
Tony

 

[Joan Wild]

Indexing the FK isn't a bad thing - it just shouldn't be a unique index.

 

[Tony Benham]

Hi Joan,
I should have said index (no duplicates) was set for the FK to be more
precise.
Thanks
Tony

 

[BruceM]

As an additional point, Date is a reserved word, and thus shouldn't be used
for a field or control name. If it is so used it needs to be in brackets
every time it is used in code. In general it is best to avoid using
reserved words for field and control names. Here are some lists of reserved
words:
http://office.microsoft.com/en-us/assistance/HP011353121033.aspx
http://support.microsoft.com/kb/248738/EN-US/
http://support.microsoft.com/default.aspx?scid=kb;en-us;209187

 

[Joan Wild]

Some further thoughts: You have Date as the primary key in the tblBonus
table (date isn't a good field name as it is a reserved word, btw).

I don't think this is a good choice as a primary key. That would mean that
only one employee can get a bonus on any given date. It's likely that what
you want is a primary key consisting of Date AND Initials. That would mean
only one bonus for any given employee on one date, but would allow for more
than one employee to get a bonus on that date. As long as employees don't
get more than one bonus on one date, this should be the primary key.

If an employee can get more than one bonus on one date, you'd make the
primary key Initials, Date, Reason.

To make a multi-field primary key, in design view select the fields in the
upper pane (Ctrl-click on the row headers); then hit the primary key button.