Subform 1 key in 2 possible fields

  • Thread starter Thread starter Douglas J. Steele
  • Start date Start date
D

Douglas J. Steele

Sounds as though you may want to rethink your table design.

Having repeating fields like Worker1 and Worker2 is a sign that your
database hasn't been normalized. Instead, you should have a second table
containing the EncounterID, the WorkerID and the TypeOfWorker (where
TypeOfWorker would be "Primary" or "Secondary")
 
I have a form that records info about Volunteers in our organization
(from table Workers). These volunteers do appointments as either a
primary worker or secondary worker.

I'm trying to create a subform that displays a list of appointments
(from table Encounters) they went on as either a Primary worker or
secondary worker (Worker.ID recorded in Encounters.Worker1 or
Encounters2). I'm not sure how to set up how to linking of Master and
Child fields.

I want to display records in the subform where Workers.ID is contained
in eiter Encounters.Worker1 or Encounters.Worker2. Does that make
sense?

Any suggestions? I'm pulling my hair out with this.

-john
 
I think I understand what you are saying. But if I did this, is there
a way to get around around having to select the proper WorkerID and
TypeOfWorker when recording the data for the appointment?

I need to make this as easy as possible for the end user (they're not
exactly skilled at data entry). Is there a way to select WorkerID from
a combo box and it would automatically enter a value of "Primary" into
TypeOfWorker?


-john smith
 
Typically you'd use a form/subform approach when you've got a relationship
between two tables such as this.

Your subform would be based on the new table I've proposed. It could have a
combobox based on the Worker table, bound to the WorkerID field in the new
table, plus a "WorkerType" text box, with its Default Value set to Primary.
 
Also, how could I do 2 entries in the TypeOfWorker table and only 1 in
the Appointment table? Especially when the Appointment data entry form
is actually a subform of the ClientDetail form?

-john smith
 
Back
Top