you have to use concept of cascading lookups.

you can start learning about it from hereThree-Tier Cascading Drop Downs: Do Go Chasing Waterfalls!

but if you search for cascading lokups or dropdowns on the forum you will certainly find plenty of other useful examples

Unfortunately that doesn't work for my scenario, where each department has the same crew names. So I've had to add the Department ID into the crew name. e.g. Prod-Bilby, WS-Bilby, D-Bilby to get it to work.

I think it's applicable to your scenario, you just need to realize how to map single lists from example provided into your scenario.

IMHO, it should be as follows:

- you will have list of departments with fields dpt_id, dpt_name, etc

- you will have list of crews with fields crw_id, crw_name, etc

- you will have list of department_crews with fields dptcr_id,dpt_id(lookup to departments), cr_id(lookup to crews)

- you will have list of employess per department and crew with fields emp_id, emp_name, dptcr_id(lookup to department_crews)

(you do not state it explicitly, but I assume you have different employees per every {department+crew} combination)

then you place lookups onto form and cascade them as follows

- lookup that will select a department - lookup control connected directly to departments list

- lookup that will select a crew within department - lookup control connected to department_crews list and filtered by previous control that selects department.

- lookup that will select employess - lookup connected to employees list and filtered by previous control that selects department_crew

Thank you. I will give that a go.

Hi Joanne Morris, hopefully you've resolved this problem.
could you post a resolution and close the question by marking it answered?