Joining 2 Tables based on a Common Relation to Another

[StudentTeacher]

Hi there--How can a table joining Clients and multiple Staff be populated based on assigning a new client to a Team? Note that staff are already assigned to Teams, and Clients and Staff can only be assigned to 1 team. Here are the relations:

tblClients: ClientID, ClientLname, ClientFname, TeamID, etc.
tblStaff: StaffID, StaffLname, StaffFname, TeamID, etc.
tblTeams: TeamID, Team

jtblClientsAndStaff: ClientsAndStaffID, ClientID, StaffID



In this setup, Clients and Staff are both assigned to Teams. However, to send reports to all Staff associated with a client through a Team, clients and staff should also be joined in jtblClientsAndStaff.

Is there a way to automatically do this based on the Client-Team relation when entering a new Client in the frmAddClients form and assigning the to a team (lstTeams)?

Is there an easier way? My mind is blank right now. Hope this makes sense. Thanks

[rpeare]

I don't understand your post. Are you asking for a way to look at a specific student be able to notify all the staff associated with the team that student is in of something?

If that's not it could you give a concrete example of what you want to accomplish rather than referencing your tables

[StudentTeacher]

Hi rpeare, essentially yes. I hardly understand it myself--writer's block.

Basically, I'd like it when a user enters a new student (client) in the addclient form and selects the client's team from a lstbox, that staff who are already entered and assigned to the team get their staffIDs linked to the clientIDs in another join table, jtblClientsAndStaff.

Alternatively, I could just insert another lstbox into the addclient form to select staff team members associated with a client. But this would require a lot more clicking. I was just wondering about an automatic way in this particular setup.

[rpeare]

If you're using the setup I originally gave you in another thread you don't need to do any additional data entry, if both staff and students are assigned to the same group you don't need to do anything new (assuming both students and staff can only be assigned to one team at a time). If you then want to find people that are in a specific team you use the TEAM table as the primary reference and link the team ID to your staff and your students tables. I'm not sure what you want your output to be or if you're using the same setup I sent you, have you got an example of your database with a specific example of what you want to accomplish with your data?

[StudentTeacher]

Ok, I get it now; thanks for the clarification. I usually work with a single "people" table and categorize them as students, staff, etc., rather than having 2 people tables. I'm also wrestling with only assigning 1 staff to 1 team, as there may be an exception of 1 staff overlapping teams. Thanks again for your time and ideas. I greatly appreciate it. ST

[rpeare]

Yes if you are carrying the same data for both sets of people then a single table is preferable with a single field to indicate whether they are staff or student. Even if that's the case my form will work as will any query you run against it, there's really no difference except that if you want to see a combined list of staff and students with two tables you'd probably have to use a union query, whereas with 1 you don't. I split them because I didn't know if you were carrying completely different sets of information for the two groups or not.

If your staff can be members of multiple teams then you have to alter your table structure a little bit Instead of the people table having the team ID you'd need a table specifically to track teams and the people on them (at least in a normalized database) So for instance if your staff member John Smith were a member of two teams you'd have a table like this:

LinkID TeamID PersonID
1      1      1
2      2      1

Where LINKID is the primary key, TEAMID is the team the person belongs to and PERSONID is the the person belonging to that group.

If there's even the remote possibility that a person will be in more than one team you will want to set up this intermediary table.