All staff in one table with type (either worker or nurse). You could have type table and assign a typeID to Staff record. Join table btw County and Staff.
Assuming each Client is only assigned one worker and one nurse:
Client table should have three foreign keys: CountyID, NurseID, WorkerID. Assign County using value list of County IDs (Name). Assign Nurse using related value list (relationship to join table btw County and Staff from Client to join cartesian clientIDxjoinkey and client::countyID = join::countyID and Client::TypeIDNurse = TypeID). Assign Worker using related value list (relationship to join table btw County and Staff from Client to join cartesian clientID x joinkey and client::countyID = join::countyID and Client::TypeIDWorker = TypeID).
Im sorry but Im still confused. Are you sudgesting I link all three County, Nurse, and Worker to the Client table through one join table?
Instead of associating with the County first.
Please answer these questions:
1. Can a worker/nurse be related to more than one County? (My post above assumes so-the only join table)
2. Can a client be assigned more than one worker/nurse? (my post assumes "no", just one of each)
3. Can a client be assigned to more than one County? (my post assumes "no").
A client could have only one nurse at any time, although they may change.
A client could have one worker and one case manager, although its usually only one or the other (thats why I grouped them together).
A client can only have one county.
A county can have many workers/managers and nurses.
Ideally, in the client record, I would like to choose the county the client resides in and have the option to choose from a list of workers or nurses, or be able to enter a new worker or nurse in if they are not in the list.
You need four tables: Staff, StaffTypes, Clients, Counties.
TypeIDNurse_c = 100 // given this is the ID for a nurse in your StaffType table
TypeIDWorker_c = 101 // given this is the ID for a worker in your StaffType table
TypeIDCaseMgr_c = 102 //given this is the ID for a case mgr in your StaffType table
Build a relationship: Client>Stf~Nurses. Client relates to Stf by CountyID. That filters the available Staff by County. However, since you want this relationship to only show Nurses, you need to filter by that StaffType, too. So the relationship has two predicates: Client::_kF_CountyID = Staff::_kF_CountyID AND TypeIDNurse_c=Staff::_kF_StaffTypeID.
This is the relationship you'll use for a related value list from Client to Nurses.
Create similar relationships for each staff type from client.
Counties (__kP_CountyID and County)
Nurses from Client by County ( related value list using the above relationship Nurses)
Workers from Client by County (related value list)
CaseMgrs from Client by County ( related value list)
If the required Staff Name does not appear, you'll need to script the creation of a staff member. You could put a button on the Client Form that pops up a new window to a Staff Form and does a New Record, or just have your user go to the Staff form and create a new Staff record. They'll need to assign them to a County, as well.
PS: I don't know how to post demo files, or I'd just have done this for you.
Question: Do you need to keep track of a history of Staff Assignments to a Client? If so, you'll need another table.
Things are working now.