Employee/Team/Teamleader relationships. (1 Viewer)

coasterman

Registered User.
Local time
Today, 01:24
Joined
Oct 1, 2012
Messages
59
Hi

I'm using a real world example of what happens in my own company so I can try and improve my understanding of table relationships.

THe scenario.

We have multiple teams all doing similar work. Each team has a team leader and each team has a team name. A team leader only ever manages one team at a time

non team leader employees are regularly moved between teams as work volumes fluctuate.

The team leaders vary rarely move changes teams and so are typically associated with the same team name (but I appreciate rarely is distinct from never) If they did ever move the team leader inherits the new team name rather then his/her new staff inheriting a different team name via their new team leader

My first thought is all people should sit in a tblEmployees having EmpID_PK and a TeamleaderID_FK so a form containing the team leader name combo box could have as its record source the EmpID from tblEmployees table. A similar approach would be applied to the Team name

What I am struggling with is if a non team leader employee is, assigned a different TeamleaderID_FK then the TeamNameID_FK isn't automatically updated. I can see the association between the team leader and the team name but cant get how to express this in a table relationship.

Do I need a another table perhaps tblTeamLeader(TeamLeaderID_PK,TeamID_FK,EmployeeID_FK) and if so how would I set up the relations to the main tblEmployees and tblTeam
 

gemma-the-husky

Super Moderator
Staff member
Local time
Today, 08:24
Joined
Sep 12, 2006
Messages
15,613
I would have a teams table, with a team name, and a team leader pointing to the employee.
Alternatively have the employee table hold the team id for the team leader.

If an employee can be in a single team then store the team id in the employee table
if an employee can be in multiple teams then you need a TeamMembers junction table to link the teams to the employees
teams ---- team members ----- employees
 

plog

Banishment Pending
Local time
Today, 03:24
Joined
May 11, 2011
Messages
11,611
I think Team Leaders and Team Members should be connected via the Team table and its junction table that identifies what employees belong to what teams.

tblEmployees
EmpID, autonumber, primary key
EmpName, Text, Name of employee
etc...

tblTeams
TeamID, atuonumber, primary key
TeamName, Text, Name of Team
ID_TeamLeader, number, foreign key to tblEmployees
etc...

tblTeamMembers
tm_ID, autonumber, primary key
ID_Team, number, foreign key to tblTeams
ID_Employee, number, foreign key to tblEmployees

Now, this changes if you care about past team leaders and their leadership dates. The same changes would apply to tracking team member histories.
 

MajP

You've got your good things, and you've got mine.
Local time
Today, 04:24
Joined
May 21, 2018
Messages
8,463
The thing about @plog design, it gives the flexibility for what I would be concerned may happen. The boss comes down and says we need Mark and Karen to lead team A. Then the next day says I want Josh to split time as a member on teams C & D. If these possibilities can happen I would consider this design.
 

Minty

AWF VIP
Local time
Today, 08:24
Joined
Jul 26, 2013
Messages
10,353
The thing about @plog design, it gives the flexibility for what I would be concerned may happen. The boss comes down and says we need Mark and Karen to lead team A. Then the next day says I want Josh to split time as a member on teams C & D. If these possibilities can happen I would consider this design.

I can't believe this would ever happen. A boss would never make that kind of change at the drop of a hat 🙃
 

coasterman

Registered User.
Local time
Today, 01:24
Joined
Oct 1, 2012
Messages
59
Thanks for all replies. Ive been working with the suggestions for quite a number of houre but sadly no ‘eureka!’ moment just yet. In fact when I attempted to do a couple of simple forms I’m probably even deeper in the weeds.

I’m clearly misunderstanding some fundamentals here. I've created the tables as suggested (although still some doubt in my mind I’ve implemented then precisely?)and put together a couple of forms which highlight a some of what I’m having trouble with

I've set up 9 teams and also allocated 9 people as team leaders from tblEmployees with a grade ‘6’ from a grade table (this wasn’t mentioned in original post but I’d like that attribute to visible in a continuous form for instance)

I did get a partially working Main/subform ‘frmTeams’ to work but there still an issue in the parent form where I cant select all the team leaders unless I have previous assigned a value for TeamLeader_FK in the tblTeams table. I could have a form where I assign Team Leaders to a team and nothing else but it seems there should be a simpler way of containing this within one continuous form?

My other attempt at a continuous form was a even less successful – I’m struggling to understand what the issue is to sufficiently describe it but hopefully the example frmAllAllocated will show whats up.

Any direction here would be appreciated. I feel if I can fully get my brain around what I guess is a fairly simple schema would help me in all my future table set ups.
 

Attachments

  • Emp.accdb
    656 KB · Views: 354

NauticalGent

Ignore List Poster Boy
Local time
Today, 04:24
Joined
Apr 27, 2015
Messages
6,280
I agree with Plog's design as well...however, I did come across a concept that worked for me that you may like as well.

One of our members, Crystal, showcased a Hierarchical Relationship table that you might like as well...
 

gemma-the-husky

Super Moderator
Staff member
Local time
Today, 08:24
Joined
Sep 12, 2006
Messages
15,613
Hi @coasterman

You were just about there. Just a small step away.

I've changed the Teams form slightly. The team name shouldn't be a drop down. It's the name of the current team, so it should just be text. You move to a new team with the navigation buttons at the bottom. (Or enter the team names field, and use a search facility) (or add an UNBOUND combo box to pick the team you want and then reposition the form with a little code), . Using the team name drop down wouldn't achieve what you wanted - it just changes the team name - see later. I added the teamID as a visible field. so you can see what it's doing.

I don't know if you have anything there to stop the same employee being a team leader more than once, or an employee being in multiple teams. You should be able to do that by adding a suitable index. I think personally I might have the team leader as a team member, but that's a matter of taste.

Your original form was navigating through the records when you used the buttons at the bottom. Changing the team leader or the team name doesn't navigate the records. It just changes the value for the current active team. It makes sense to have the team leader as a dropdown, in which case it either needs to show the existing team members, or all the potential team leaders (which it may already do)
 

Attachments

  • GTHEmp.accdb
    792 KB · Views: 241
Last edited:

jdraw

Super Moderator
Staff member
Local time
Today, 04:24
Joined
Jan 23, 2006
Messages
15,361
@coasterman

I recommend this tutorial from RogersAccessLibrary to experience a process to identify tables and relationships from a description of the "business". You have to work through the tutorial/process, but you will learn by doing, and the process will work for any database.

Also, as others have hinted, things/facts can change from first thoughts to the implementation stages. If something could change, better to consider same in your design and have some flexibility.
Ask lots of questions when gathering facts ---surprising that different viewpoints often lead to hidden assumptions, different concepts/meanings/synonyms.....

Good luck with your project.
 

MajP

You've got your good things, and you've got mine.
Local time
Today, 04:24
Joined
May 21, 2018
Messages
8,463
I think personally I might have the team leader as a team member, but that's a matter of taste.
@gemma-the-husky,
I am trying to think the pros and cons of both. I could go either way. If you add the team lead to the team members table then you could simply have a boolean field for "isLead". This still gives the option for multiple team leads (but restricting would be harder because you cannot index). Then you could get rid of the team lead FK in the team table.
 

The_Doc_Man

Immoderate Moderator
Staff member
Local time
Today, 03:24
Joined
Feb 28, 2001
Messages
26,996
I had this for a set of (varying) 4 to 6 teams among 30 people. I make no claims of my way being better or worse, but it worked very well.

I was able to define a "Person" table and a "Team" table. I then had a "TeamMembers" table. The TeamMembers table had fields <PersID (FK), TeamID (FK), TeamRank (BYTE integer)>. The form was where all the logic was kept because this was definited a "users do not see infrastructure" case. I had command buttons on the form: ADD TEAM which created a new team record with team name & team specialty. You could not SAVE the addition until you specified a team leader from a Person-table dropdown combo. That person was added to the junction table with TeamRank 1. DROP team simply removed all records for that team from the junction, then dropped the record from the Team table in that order. I could also ADD MEMBER with dropdowns for Person and Team, but that add would be TeamRank 2. I could drop a member from a team easily enough because it was just "pick the team" and "pick the person" - but there, the dropdown only showed current team members.

There were other tedious things it would do such as if I dropped the person who was the team leader, I popped up a warning: Select new team leader, and then blocked everything until that was done. The point is to keep the tables simple and put smarts all over the forms. The form enforced the "single leader" concept and watched out for other things like removing the last member of a team or trying to add a new leader to team that already had a leader. It asked if the leader would still be a member or was leaving the team altogether and did the right thing - either remove the record or change the former leader to rank 2.

Of course, the form recorded each team membership change in a logging table which got archived every month or two. It never got so big that we had to archive twice in a single month.

Just to put this in context, we had 800-1500 servers varying over time as the business improved. Servers were managed by teams, and everyone on the team would get notices when something ordinary was scheduled. However, the boss only called in the team leader when there was something going on and the leader would then brief the members separately. "Something" usually was a security audit being scheduled or a particular user complaint about performance or something outside of everyday "keeping up with the patches."
 

coasterman

Registered User.
Local time
Today, 01:24
Joined
Oct 1, 2012
Messages
59
Thanks so much for the help I'm finding this all very useful and will be working through the suggestions and see where that takes me. I'm so keen to get these basic principles down rather than tinkering around the edges when anyone asks me 'help them out with a quick database' I'm not sure what it is about tables and relationships but I find them fascinating.
 

Users who are viewing this thread

Top Bottom