Database structure for this project (partial) Minimize

This diagram doesn't show all the tables and fields due to size and clutter.

The devil is in the details.....HINT:  When you plan to use the same look-up tables (for drop-down lists, etc)  in more than one place, use different field names for the field  in different tables  
For example,  vth_clinicians is used as a look-up table to populate dropdown lists in multiple places in my application, (vth_admission and vth_census) ,   Initially one of the linking fields in each table was named ClinicianID (I think it was the current admitclinicianID and attendingclinicianID).  At some point I started getting 'ambiguous variable' errors when I would  run some involved queries.  Access was having trouble telling WHICH clinicianID I was talking about when I was combining tables in the queries.  By renaming the fields in the linked tables (they are the same variable type, just a different name) the problems resolved.  

cllinicianID    -- field in the vth_clinicans - this is the look-up table

admitclinicianID            -- fields in vth_admission using vth_clinician as look-up
dischargeclinicianID    -- fields in vth_admission table using vth_clinician as look-up

attendingclinicianID   --- field in vth_census table using vth_clinican as look-up

When you are using a look-up more than once in a single table (admitclinicianID and dischargeclinicianID above), you obviously need to have different names for the fields.   Another examples is censusadmissionID and censusadmission2ID in the census table below that both use information from vth_admission.

Database structure for census project