Arrange Relationships View

[Monkey36]

I've got a table of Patients who may have up to 3 Consultants assigned to them, so have numeric ConsultantID1 - ConstultantIC3 fields which match to the UniqueID in the Consultants table.

I'm trying to arrange the tables in the Relationships Viewer, and ideally what I want is to have three lines displayed, one from each of the Consultant ID fields on the Patient table, converging on the UniqueID field on the Consultants table.

I can do this, and everything looks great, but when I save, close Relationships and then click back into Relationships there are now three copies of the Consultants table showing!

Am I trying to do something that Access won't let me here? I wouldn't mind so much having three tables except that there is an AreaCode field in Consultants that matches to a further table, and when I try to add this in the Relationships Viewer get itself confused (it's a bit longwinded to explan here).

If anyone can set me straight I'd appreciate it!
Cheers,
Monkey.

 

[lupins46]

You need three copies of the consultants table.If you just use one copy then what you are telling Access is that for a consultant record to be related to a patient record the consultant id must match all 3 of the fields in the patient record.

 

[lupins46]

If you want avoid all sorts of searching problems you should change the design of your database so that the PatientConsultant link is held in a separate junction table. This means using a subform to list your consultants on your patient form. But the day will come when there are 4 consultants and then you will just be able to add another with no trouble at all.

 

[Monkey36]

Ok, I hear what you're saying.

I wasn't sure if entering all three links together meant they all had to apply, thanks for clearing that up for me. I was just trying to find the clearest way to illustrate it on the Relationships map.

I really like the PatientConsultants link table idea, though. I've got a VB6 front end which I'll have to spend a few minutes thinking about before I can see the best way to implement this, but I think this will be the best way around the problem.

Thanks a lot for your input (no to mention speedy response!)

Take care.

 

[lespaul]

what lupins described is the normal database set up for a one to many relationship (one patient may have 3 consultants). you should read the document linked below for more information...

Patient
PatientID (PK)
Name
Address
etc.

Consultant
ConsultantID (PK)
Name
Address
etc.

PatientConsultants
PatientID (FK) }
ConsultantID (FK)} composite PK

HTH

Leslie

Anything worth doing is a lot more difficult than it's worth - Unknown Induhvidual

Essential reading for anyone working with databases: The Fundamentals of Relational Database Design