One-to-Many problem

[thekey]

Hello users

I am in need of some help with an issue. I am using MS Access 2007 to create a database. I have tried to respect good practices and separated data into a number of tables. My main table has a primary key specified as person_ID which I want to use to link in relationships.

In my database I need to create a form for data entry which I have done but the problem I am having is that in my database each person can have more than one visit which needs to be logged.

I want my form to look something like this:

person_ID
Name
Address
DOB

Visits
1
2
3
4
etc

I have created another table called visits which has the following fields

person_ID
visit_ID
visit_type
visit_date

I understand i need to create a subform in my form to add this data but I am struggling with the relationship type. MS Access only seems to create a one-to-one relationship when I drag and drop it. I was under the impression it needs to be a one-to-many relationship as one person can have mny visits. Does anyone know what I am doing wrong? Please spell it out for me as I cannot seem to understand it. I have searched the www and books but can't understand where I am going wrong.

Thank you in advance

 

[WIS]

Hello users

I am in need of some help with an issue. I am using MS Access 2007 to create a database. I have tried to respect good practices and separated data into a number of tables. My main table has a primary key specified as person_ID which I want to use to link in relationships.

In my database I need to create a form for data entry which I have done but the problem I am having is that in my database each person can have more than one visit which needs to be logged.

I want my form to look something like this:

person_ID
Name
Address
DOB

Visits
1
2
3
4
etc

I have created another table called visits which has the following fields

person_ID
visit_ID
visit_type
visit_date

I understand i need to create a subform in my form to add this data but I am struggling with the relationship type. MS Access only seems to create a one-to-one relationship when I drag and drop it. I was under the impression it needs to be a one-to-many relationship as one person can have mny visits. Does anyone know what I am doing wrong? Please spell it out for me as I cannot seem to understand it. I have searched the www and books but can't understand where I am going wrong.

Thank you in advance

Person_ID in the Person table should be Autonumber (Long, No Duplicates), a Primary Key (PK). Person_ID in the Visits table should be Number (Long, Duplicates Allowed), a Foreign Key (FK). This will give a 1:many join.

It looks like you have the FK indexed as No Duplicates.