Cannot create one to many relationship

  • Thread starter Thread starter TonyB
  • Start date Start date
T

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
 
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.
 
Hi Joan,
I should have said index (no duplicates) was set for the FK to be more
precise.
Thanks
Tony
 
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.
 
Back
Top