Design question

[Dee]

I am trying to design a database to track sites, their addresses, the
personnel at the addresses and also information about certain personnel at
the sites as well as information about each site. I have a table for sites, a
table for addresses and a table for personnel. I need to track information
about only certain personnel, for example, there are investigators,
sub-investigators, study coordinators, pharmacist etc. I need to enter
information only about the investigators and sub-investigators. For example,
I need to enter the speciality of each of the investigators and
sub-investigators only not the other personnel. Sometimes an investigator
will have more than one specialilty. For example, he may specialize in
oncolgy, hematology etc. I am getting a little confused as to what tables I
should set up. Should I create a separate table just listing the sites and
the investigators and sub-investigators?Should there be a table for
specialities, since each investigator could have more than one?

I don't know if I am being very clear but any help would be appreciated. I
am using Access 2003.

Best regards,

Dee

 

[Damian S]

Hi Dee,

Set up a couple of new tables, one for Specialties, and one to link
Personnel to Specialties.

tlkSpecialty
SpecialtyID (autonum, PK)
Specialty

tblPersonnelSpecialty
PersonnelID (num, links to Personnel Table)
SpecialtyID (num, links to Specialty Table)

This way, you can optionally select one or more specialties for each
Personnel entry. Use a subform on your personnel form to capture
specialties. (Use a continuous form with a combo box.)

Hope this helps.

Damian.

 

[Dee]

Dear Damian,

Thank you for your help. I have another question. I have a form already that
has the site. the site address and a subform with the personnel for that
address. The only personnel who would have a speciality would be the doctors
which would be the investigator and the subinvestigator. There are other
personnel for example, coordinators, pharmacists. etc. Because the personnel
is already a subform, would I create another subform. The reason I have
personnel as a subform is one site could have more than one address and one
address would have many personnel. This is where I am getting a little
confused. I want to be able to add the specialties of each of the
investigators and subinvestigators from each site.

Thanks again for your help.

Best regards,

Dee

 

[Damian S]

Hi Dee,

The table structure I described would work regardless of whether you have
already got a subform - you can have another subform inside the original
subform - if you are concerned that people will inadvertantly select
Specialty items when they shouldn't, try hiding/disabling the Specialty
subform when you aren't looking at a Doctor... eg: in the Personnel form "On
Current" event, have something like this:

if me.COMBONAME = DOCTORIDNUMBER then
me.subfrmSpecialty.enabled = true
else
me.subfrmSpecialty.enabled = false
endif

Hope this helps.

Damian.