Using same Contact Name in several Teams (1 Viewer)

Danick

Registered User.
Local time
Today, 17:41
Joined
Sep 23, 2008
Messages
351
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

Lifelong Learner
Local time
Today, 14:41
Joined
Mar 14, 2017
Messages
8,738
Maybe the table ought to be tblTeamContacts and contain one row per ContactID...
 

isladogs

MVP / VIP
Local time
Today, 21:41
Joined
Jan 14, 2017
Messages
18,186
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

Registered User.
Local time
Today, 17:41
Joined
Sep 23, 2008
Messages
351
Thanks - I'll give it a shot. But that will mean re-structuring the tables...
 

Gasman

Enthusiastic Amateur
Local time
Today, 21:41
Joined
Sep 21, 2011
Messages
14,047
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

I’m here to help
Staff member
Local time
Today, 14:41
Joined
Oct 29, 2018
Messages
21,358
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

Registered User.
Local time
Today, 17:41
Joined
Sep 23, 2008
Messages
351
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

MVP / VIP
Local time
Today, 21:41
Joined
Jan 14, 2017
Messages
18,186
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've got your good things, and you've got mine.
Local time
Today, 17:41
Joined
May 21, 2018
Messages
8,463
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

Registered User.
Local time
Today, 17:41
Joined
Sep 23, 2008
Messages
351
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

Registered User.
Local time
Today, 17:41
Joined
Sep 23, 2008
Messages
351
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

MVP / VIP
Local time
Today, 21:41
Joined
Jan 14, 2017
Messages
18,186
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.
 

Users who are viewing this thread

Top Bottom