Reversed one-to-many relationship 1

[lrumd]

I have the following tables and relationships. Everything looks fine EXCEPT the relationship between tblPatients and tblAppointments (I need 1 patient to have many Appointments, not the other way round)

tblPatients
PatientID tblPhysician
PhysicianRef---------PhysID
LastName
FirstName tblAppointments
AppointmentRef--------------------ApptRef
HospitalRef---| ApptDate
... |
... | tblHospitals
... |-----HospitalID
...
DignosisRef-|
|
| tblDiagnosis
|--------DiagnosisID
...
...

All the xxxxID are AutoNumber, all the xxxxRef are Number, Long Integer

Any ideas??

Luis



"To confess ignorance is often wiser than to beat about the bush with a hypothetical diagnosis."
William Osler, 1895

 

[lrumd]

Sorry, AppointmentRef in the tblPatient RELATES with AppointmentID in tblApointments

Luis

 

[RickSpr]

Since you need one patient to have many appointments, that should clue you that you can't put an AppointmentRef into tblPatients, since that would only give you room for one appointment.

Instead, you need to put PatientRef into your tblAppointments, and base the relationship on that.

Actually, I have to wonder whether a patient can have only one doctor and only one diagnosis, too, but you know your data better than I do.

Rick Sprague
Want the best answers? See faq181-2886
To write a program from scratch, first create the universe. - Paraphrased from Albert Einstein

 

[lrumd]

Thanks, Ricky. Now that I understand it, it appears to be pretty obvious. I feel like a fool. An enlightened fool.

And yes, a patient can have several Drs. and Diagnosis, but for the purpose of my database, I am tracking certain patients, their referring physician, their main diagnosis and the tests and appointments

Thanks
Luis


"A Doctor who treats himself has a fool for a patient"
William Osler, MD