Populate a field based on entry in another record

[BigBill57]

I have a table that lists passengers on a skydiving trip. In order to make sure that the capacity of the aircraft is properly accounted for, each jumper must be listed. Also listed is a code field that tells the staff who is doing what for each jumper; the jumps are tandem jumps, where the client is strapped to a "jump master." There also may be another jumper taking pictures/movies of the client. Below is a sample from the table that the dispatcher/coordinator completes for each load on a given day. I need to populate a hidden field that will be used later for payroll with the client's name in the same record as the "jumpmaster" and the "photographer."
Jumper Code
Kay R T1 { the client.
ALEC TM {the "Jump master" - hidden field ="Kay R"
JUSTIN P VDO {the photographer - again, hidden field= "Kay R"
James R T1 {the client
JVC TM [jump master
TaylorC T1 {client
JUSTIN P TM {jump master
JOSH VDO {photographer

This is repeated for each client all day. There may be 27 clients (or more) in a given day.

Thanks for your help.

 

[DrSimon]

This should really be in Access Tables and Relationships.
Haven't got time to look at this properly, but the way I see it is that you should have multiple tables:

Client_Table
Client_ID (1ry Key)
Client_Surname
etc

Staff
Staff_ID (1ry Key)
Staff_Surname
etc.

Jump_Table
Jump_ID (1ry Key)
DateTime (if you want)
Client_ID
Jump_Master_ID
Photographer_ID

Now you join the Jump table to the others by their primary keys. Not sure what you mean by a 'hidden field' as it needs to be there, but if you need to hide it from the client use a report of form. Using tables like this you can store information about your client and staff separately.

 

[BigBill57]

Thanks for responding; however, the customer's needs prevent me from using multiple tables (which I know how to do). The field does not have to be hidden; don't know why I dropped that in. I've listed it in Access Tables and Relationships based on your recommendation.