One to Many relationship Access 2010

[suzemt]

I am a fairly proficient user of MS Access, but am having trouble creating a one to many relationship between two tables, which I thought would be straight forward.
I want a table listing members of staff with their ID number. The next table will have an entry for each time they take sick leave (ID, start date, end date, reason).
I want to be able to find a member of staff and then show each entry on the second table that relates to a sick leave.

I understand about forms and subforms etc. I just cant seem to be able to get the relationship right. If I create the relationship using the staff ID, when I click on the link between the two table to edit the relationship, it will not give me the option to change it from a one to one.

In the staff table the ID is the primary key and in the second table I just set the primary key to Autonumber.

 

[zoran.eremija]

For your problem I suggest you the following model as a basis

 

[rkl122]

.........In the staff table the ID is the primary key and in the second table I just set the primary key to Autonumber.

Not to patronize, but there's also a foreign key field in the second table? To which you connect the primary key in the staff table?

 

[suzemt]

Please. I did say fairly proficient, so please patronise all you like. If helps, I am not proud

I will take a look at both suggestions.

Thanks for taking the time to get back.

 

[Pat Hartman]

You don't get to choose the cardinality of the relationship. The database engine chooses based on the relationship you drew. If you connected the tables PK-to-PK, then the relationship can only be 1-1. 1-Many relationships are created by connecting PK-to-data field which we call FK at that point. Many-Many relationships are two 1-Many relationships connected via a Junction table and so they are always three tables rather than the two needed for the other two types.