Newbie query re conditional field

P

Peter Baldwin

Hi , I hope that someone can point me in the right direction
I have a database to record 'operations' performed by a group of personel
The 'operation' may be performed by a team comprising of two of the
personel- Main operator and assistant
The personel list is in a separate table to the operations (ID, Surname,
first name, Title, rank)
I currently use list boxes (based on an alphabetically sorted query of the
personel table) on a form to select the 'main operator' and 'assistant' for
a particular operation
The list box stores the personel ID returned by the listbox in the Main
operator or Assistant field in the operations table
These fields are related to the personel table to allow data to be retrieved
regarding the operating team
Whilst this works if all I want to do is retrieve either the 'main operator'
or 'assistant' eg. in a query, I cannot retrieve details on both the main
operator and assistant at the same time
How can I specify a control in a form or report to show details eg Surname
from the personel table using the ID no for the member of staff that has
been stored in the operation table?

Hope this is clear
Any advice gratefully received
Cheers
Pete
 
M

Mike Labosh

Whilst this works if all I want to do is retrieve either the 'main
operator'
or 'assistant' eg. in a query, I cannot retrieve details on both the main
operator and assistant at the same time
How can I specify a control in a form or report to show details eg Surname
from the personel table using the ID no for the member of staff that has
been stored in the operation table?
Click to expand...

1. Make a Select Query that has your Operations table and TWO copies of your
Personnel table.
2. Link the PersonID of the first Personnel table to the MainID of your
Operations table.
3. Link the PersonID of the second Personnel table to the AssistantID of
your Operations table.
4. Save the query
5. Open your form in design view and set this query as its Record Source.
6. Add your other control to the form and bind it to the other column(s) in
the second copy of the table in the query.
 
Top