Relation Between Pharmacist and Patient in access 2016

Question

I am creating a Pharmacy Database in Access 2016. It is my school Project and first Database Project.

My first problem is that we know that a Pharmacist can have many Patients, so it means that the relationship between Pharmacist and Patient is one-to-many. So in order to create a one-to-many relation, I made Pharmacist_ID as Primary Key.

Now the problem is that we know that the relation of Address and Patient is one-to-one, so how can I accomplish this task?

Another problem is that I already have the address, the city and nationality which are linked with the Pharmacist_ID. Can I link these tables with Patient_ID?

I am confused because the data-type of Pharmacist_ID is Auto-Number. The Patient_ID of the first Patient will be 1 and then Pharmacist_ID of the first Pharmacist will also 1 so what will happen?

Again, I am on MS-Access 2016. This is the Picture of The RelationShip and you can see the Details of my Tables

Regards,

Arslan Iftikhar

This is for Thomas G check it out Thomas do you think I am doing right or wrong

Answer 1

I will make below changes to Address table:

1. I will prefer creating one common table for Address which also has City and Nationality (for simplicity else link them like image 2 below)
2. Added field PID as Number where you can save Pharmacist or Patient ID
3. Added field Ptype as Number where to save value 1 when Pharmacist and 2 when patient, so we can easily differentiate using this field.

Image 1

Image 2