Managing Group Membership

[MZYORK]

Hi Everyone,
I am fairly new to working with Access and I am having a bit of an issue. I have a database that tracks events. Each event has a team, but the team assignment changes with each event so I can not make a table with the team membership and just assign THAT team to an event. I have a table (tbl_TAVS) that keeps all the records of the events. The primary key is the TAV ID. I have a second table (tbl_TAV TEAM) that keeps track of the team members assigned. The TAV ID in used to link the TEAM back to the event. I have included a screen shot so you can see what I have done. As you can see, there is information for two TAVs (80 & 67). Some of the members show up in both or even twice in one but under a different position. First, I would like to ensure that I have it set up in the proper manner. Second, I would like to know how I can make an input form that will let me input all the members and their position (between 1 -10) and associate them with the same TAV ID at one time. So if I have a form, I input the names in individual boxes, and hit next and they are all placed into tbl_TAV TEAM with the right TAV ID. Oh, I hope this makes sense.

[ranman256]

you would make a new team group when the group changes, they would have a new TeamID
then add the employees again
It seems you have that with team 80, and team 67

Id make a form with 2 list boxs, and a combo for Team names (ID)
lstEmps are the list of available employees.
cbo box to pick the team.
lstTeamMem is the target list of emps picked to be in the team

user picks a team
dbl-click an Emp from the emp list, this runs an append query to add that emp to tTeam table using EmpID (from lstEmp) and TeamID (from combo)

delete from lstTeamMem by selecting in lstTeamMem ,press delete key, this runs a delete query to remove them from the list.

a COPY button could also run an append query to add ALL emps from 1 team to another, then just remove or add to adjust

[MZYORK]

ranman256,
Thank you for the reply. I think I understand what you have said. There is another issue that throws a wrench in the plan you mentioned. My bosses want to keep a history of the team makeup so I cannot afford to delete the team makeup each time. tbl_TAV TEAM was made the way it was so that I can run a query against it. The query works PERFECT. The bosses want to be able to search for all members that were assigned to a specific event. With the setup I have now, they can search for member by event name, individual members based on their name, all individuals who held a certain position, the whole team for that event and a combination of the already mentioned. The teams change drastically each time. Its like the members are thrown in a hat and pick one by one for each position.