Field from multiple records to multiple fields in one record

[HDennen]

Hello all,

I am very stuck.

I've got a data entry form for events with a nested data entry sub-form for the contents of the vehicle going to the event.
These are from EventsLog table and VanLog table respectively.

EVENT MAIN FORM
EVENTSLOG
EVENT_NAME
EVENT_DATE
VAN1_NAME (yes/no)
VAN2_NAME (yes/no)
VAN3_NAME (yes/no)
VAN4_NAME (yes/no)


EVENT SUBFORM (tabs for each van, separate record in VANLOG for each van selected in main form)
VANLOG
EVENT_NAME (linked to main)
EVENT_DATE (linked to main)
VAN1_NAME (yes/no linked to same van in main)
VAN2_NAME (yes/no linked to same van in main)
VAN3_NAME (yes/no linked to same van in main)
VAN4_NAME (yes/no linked to same van in main)
CONTROLLER1 (foreign key)
CONTROLLER2 (foreign key)
CONTROLLER3 (foreign key)


CONTROLLER4 (foreign key)


CONTROLLERS
ID
S/N
SUBLOC

I need the S/N's of all controllers with a subloc = van_name to populate the CONTROLLER1, CONTROLLER2, etc. fields in the VanLog table.

I hope all that made sense. I'm really not sure how to do this. Any help would be HUGELY appreciated


-H

[RuralGuy]

And if you need 5 Van Names what do you do? You may want to revisit the table structure one more time for normalization.