Design /relationship

[jonamua1971]

Hi all,
i have been developing a new database and the relationship are giving me some problems when i try to enforce intergrity rule .
iam developing a database for a small assisted living facility and here is what we want to achieve. be able to get information for parents/children/physician and location where they live. the facility has three different location and one central place.i have the following tables:
tables for patients,locations,physician,appointments, vehicles,guardians and medical info.i have also been getting a message, no unique index found for the referenced field of the primary table.
this is my second database am doing except this one has more tables than the previous one.
also i wanted to find out the easy way to change the auto subform made,how to change the view from dataview to single view.
i thank you all for your help

 

[The_Doc_Man]

Let's do a "divide and conquer" on this one.

From your description, you should have tables something like this:

tblPatient: PatID (PK, autonumber possibly), name, location ID (FK), physician (FK), primary guardian of record (FK) *but see note in guardian table, other info.

tblLocation: LocID (PK, autonumber possibly - but for four locations you could do this by hand), info about location like address, capacity, etc.

tblPhysician: PhysID (PK, autonumber possibly, but I seem to recall that physicians need some sort of federa ID for Medicare purposes and that might also be a viable candidate key), name, address, phone, etc.

Guardians: GuardID (PK, autonumber possibly), guardian for ID (=PatID), name, address, phone, primary guardian flag (Yes/No), etc. NOTE: if you include the Patient ID here, a simple query would allow you to LIST the guardians of record for a single patient. HOWEVER, if one guardian can be the guardian of record for two patients, and more than one guardian can be listed for some patients, you have a many-to-many case requiring a junction table:

tblGuardPatient
PatID: FK
GuardID: FK
Primary: Yes/No

You made passing references to Children and Vehicles. Not enough info here for me to understand.

Appointments: Definitely a junction table, but the question is with whom? Obviously, the patient is one side of that coin. The other side could be a physician - for a medical visit - or your company's representative - for other kinds of visits. This isn't clear.

Assuming medical appointments, you want

tblAppoint: PatID (FK), PhysID (FK), date/time, fulfilled flag (Yes/No), perhaps a location code - an encoded byte with meanings: (1) at patient's room (2) exam room at central office of that location (3) at doctor's office (4) other. As an option, include a primary key in this table (autonumber should work) and use the AppointID in the "rigorous" medical info case I describe later.

You would track whether the patient kept the appointment via "Fulfilled" flag - if that is important to you.

Medical info: This could go all over the map.

Simplest case: tblMedInfo, PatID (FK), date/time, memo field for notes

More rigorous case: tblMedInfo, PatID (FK), PhysID (FK), AppointID (FK), memo field

Note that in the rigorous case, PhysID is redundant since you can get it from the appointment table. So it is your call as to how you want to do this.

A further wrinkle: If you have a staff of doctors on-hand and they can swap off among the patients, "primary" physician might be tough. Again, that would be a junction table to list the doctors who regularly see the givent patient AND the patients regularly seen by one doctor.

Changing the view on a sub-form involves going to the free-standing sub-form and going into design view and changing it. You cannot so easily change it from design view of the parent form because there, the sub-form is represented by a place-holder only.

You can also change the sub-form from event code on the FormLoad event but that seems to be more work than it is worth.

 

[jonamua1971]

Thank you!!