Using same Contact Name in several Teams

[Danick]

I have two tables. tblContacts and tblTeams.

Each record in tblTeam has 3 contacts. And I use a drop down to populate the ContactID from the tblContacts table in each of those 3 team contacts.
So the tblTeams table looks like this:

TeamID, Team1, Team2, Team3
01, 02, 03, 04 (These are the ContactID from the tblContacts)
02, 02, 04, 05
03, 05, 07, 08
04, 03, 04, 08

Notice that one ContactID can be in several teams - there is no pattern.

The problem I'm having is trying to populate the names from the tblContacts in a query instead of just displaying the ContactID.

Any ideas?

 

[Isaac]

Maybe the table ought to be tblTeamContacts and contain one row per ContactID...

 

[isladogs]

You will have problems as your data is in spreadsheet format. You need to normalise the table.
Change the tblTeams structure to tblTeamContacts
TeamID autonumber PK field,
ContactID, -number field
TeamNumber -number field

and have one record for each team and contact.

EDIT Same as Isaac wrote whilst I was typing

 

[Danick]

Thanks - I'll give it a shot. But that will mean re-structuring the tables...

 

[Gasman]

Think of it like building a house where the foundation is made of paper mache bricks.

You would be prudent to use concrete instead.?

 

[theDBguy]

Thanks - I'll give it a shot. But that will mean re-structuring the tables...

Normalizing a database typically does mean restructuring the tables, but you should benefit a lot from the effort.

 

[Danick]

Well I tried and it works, but I don't like it.
If forgot to mention, that I'm using the Labels "Team 1", "Team 2" as actual placeholders with real function names. I really should have used this example instead of the in the original thread

TeamID, ProgMgr, Sales, Coordinator
01, 02, 03, 04 (These are the ContactID from the tblContacts)
02, 02, 04, 05
03, 05, 07, 08
04, 03, 04, 08

Notice that the same contact can have a different function in each group.

If I do it using tblTeamContacts, then yes, it does allow me to make a query work, but there is no way to assign functions to each contact.

I need to be able to put contacts in teams (groups) but assign them to a specific function in that group. Is there another way of doing this?

 

[isladogs]

Incorrect. It will allow you to assign roles/functions to individual users/groups.
For a more specific response you will need to give more details of your needs.

You may not like it and are free to continue with your original spreadsheet design BUT if you do so, everything you try to do will be a battle against how Access is designed to work. You would be better off using Excel instead if that is the case

 

[MajP]

You should fix it, but if not then drop the contact table into the query four times. Join one to each of the four fields. Then alias each column.

 

[Danick]

Incorrect. It will allow you to assign roles/functions to individual users/groups.
For a more specific response you will need to give more details of your needs.

You may not like it and are free to continue with your original spreadsheet design BUT if you do so, everything you try to do will be a battle against how Access is designed to work. You would be better off using Excel instead if that is the case

Yes you are correct, I am able to add another column to the tblTeamContacts and call it "Function" or something like that. And I'll be able to assign functions to each team. But it just doesn't look good on a form or report where each function is in a specific slot on the page. Some slots are empty and each function is easily found because it's always on the same place on the page. Now I can have all the contacts assigned to the correct function, but I can't find a nice way to present it on a form or report.

 

[Danick]

You should fix it, but if not then drop the contact table into the query four times. Join one to each of the four fields. Then alias each column.

WOW - I can't believe that worked!! I have to admit, the structure is horrible (I lied - I had to drop the contact table into the query 10 times!!) . But it looks great in a PDF report!!

I may end up going to the better table design, but I'm so grateful for this forum and folks like you thinking outside the box.

Have a great day...

 

[isladogs]

If you retain the current structure you will need to add additional fields each time a new role/function is added to your setup e.g. Finance, HR, IT etc. You will then need to modify every query, form, report to include each of these. That will be very time consuming and a total PITA.
Or you can spend a small amount of time now and fix it properly, saving yourself many hours in the future.