How to avoid repetition in query for multi value field

[newbieoxford]

Apologies for any blundering errors - I am totally new to Access and it's all rather daunting

I've created a contacts database. Some of the contacts belong to a research team. Within the research team there are different roles e.g. NationalPartner InternationalPartner AdvisoryPanel SupportStaff Researcher. Some people have more than 1 role. There is a master table 'Contacts' and I've created an additional table called 'Roles', and created a multi value field 'Roles' in the 'Contacts' table, sourcing the lookup from the Roles table.

Now I want to create a query that shows all people that are part of the research team. I've added into Roles field into the Query Design with the Critera "NationalPartner" Or "InternationalPartner Or "AdvisoryPanel" etc listing all of the possible roles.

The problem is that I have duplicates in the results when one person has more than one role. E.g. if a person is a NationalPartner and in the Advisory Panel then they appear twice in the results.

QUESTION: how can I create a query to show all people part of the research team and avoid repetitions?

I've been searching forums for ages trying to find a solution so if anyone can shed light I'd be very grateful.

 

[plog]

You shouldn't use multivalue fields. It's almost like Access is trolling its critics. It hears what everyone says about how Access is bad and then doubles down on it by adding more bad features just to see if their heads will explode.

Every value in a multivalue field should be in its own row. Suppose you had this:

Roles
Contact, Roles
"Jim"; "Advisor, Partner, President"
"Sally"; "Advisor, Partner"

That data should instead be stored as such:
Roles
Contact, Role
Jim, Advisor
Jim, Partner
Jim, President
Sally, Advisor
Sally, Partner

That's how data should be stored. Now for your question:

QUESTION: how can I create a query to show all people part of the research team and avoid repetitions?

We cannot tell you how to do that because you haven't provided us with any information about teams. You talked about contacts and roles. It might be you need a new table to define what teams are, but with what you have given us, it is not possible to discern that.

I suggest you post your data (or just a sample) and then what you expect your query to produce based on that sample data. Use this format for posting:


TableNameHere
Field1Name, Field2Name, Field3Name, ...
Jim, 13, 1/2/2016
Sally, 29, 1/3/2016
Fred, 48, 1/19/2016

 

[newbieoxford]

Thanks for your response. I guess I have to create a junction table then... I thought multi-value would be quicker/easier. I hear the resounding disapproval!

So these are the tables I have:

TableContacts
ContactID, Name, etc
1, Joe Blogs
2, Mara White
3, Claire Simmons
etc

TableRoles
RoleID, Role, Description
1, OxfordTeam, blah
2, OxfordTeamMeeting, blah
3, OxfordAdvisory, blah
4, BangladeshTeam, blah
5, PrincipalInvestigator, blah
6, ProgrammeManagement, blah
7, International Partner, blah

etc

Then you're saying I should create a junction table?

ContactRole
ContactID, RoleID
1, 2
1, 3
1, 5
2, 3
2, 6

Is that right? Quick question: is there any quick way of inputting the data for this new junction table? Manually it will take so long. I have existing queries for each of the separate roles which I created using criteria in the multi-value role field of Query Design. But when I try and copy the ContactID column from the query into the new junction table it doesn't let me do it.

Anyway.... back to the original question.

I'd like to get a query to show every person that is in the research programme. So the contacts table has hundreds of people, and only 120 are in the research programme, and all those in the research programme have one or more roles assigned.

Does that make sense?

Many thanks

 

[plog]

1. Most likely yes to the junction table.

2. I don't know what your data currently looks like. So, I can't help you get from A to B, because I have no idea what A is. If you could post sample data I could help you get it to the new good structure.

3. Again with new unrelated terms. What is the 'research programme'? I don't see it in your list of Roles. Is it the same thing as the research team you mentioned in your prior post? Without context I have no idea how to find out if some one is in the research programme. Unexplained synonyms are the enemy of communication.

 

[newbieoxford]

Hi plog,

So I’ve killed the multi-value field and created a junction table
Sorry about the confusion with terms, let me try and clarify. I have a contacts table with about 500 people. Within these 500 are 120 people that form the research team. Within this research team of 120 people, there are different roles. Everyone in the research team has a role and some people have more than one role.

My sample data:

TableContacts
ContactID, Name
1, Joe Blogs
2, Mara White
3, Claire Simmons

TableRoles
RoleID, Role
1, OxfordTeam
2, OxfordTeamMeeting
3, OxfordAdvisory
4, BangladeshTeam
5, PrincipalInvestigator
6, ProgrammeManagement
7, International Partner
NB these are roles within the research team. Everyone associated with one or more of these roles is part of the research team.

Contact_Role
ContactID, RoleID
1, 2
1, 3
1, 5
2, 3
2, 6
etc

When I do a query to return everyone in the research team, in query design, I add the Name field form the TableContacts, and I add the RoleID field from the Contact_Role junction table with Criteria 1 or 2 or 3 or 4 or 5 or 6 or 7. The aim it to return everyone that is part of the research team, ie everyone that has an assigned role.
The problem is that when one person has more than one role, it returns duplicate entries. For example, Joe Blogs is assigned to OxfordTeamMeeting and OxfordAdvisory roles, so in the query results Joe Blogs appears twice. How do I create a query to show everyone in the research team without duplicates?

 

[plog]

You make that query an aggregate query (click the Sigma/Summation in the ribbon) and GROUP BY the name.

 

[CJ_London]

duplicate thread here

http://www.accessforums.net/showthread.php?t=62948&p=339312#post339312

@newbieoxford - please read this thread

http://www.excelguru.ca/content.php?184

 

[newbieoxford]

Hi plog,

Thanks your your response again, much appreciated. Could you explain that a bit more as I'm not clear on the steps.
I've clicked Totals/Sigma, which introduces a Total row in the query design.
What's the next step? I've tried Group By 'First' but it it still comes up with the same results - multiple entries when a person has multiple roles.
I'm wondering whether the easiest option would be to create another role 'research team' and assign everyone to it, then I can just create a query with criteria for that role, rather than trying to bring all the roles together in one query. Hmm

 

[plog]

I'm still unclear about determining if someone is on the research team:

NB these are roles within the research team.

What does that 'NB' mean? Are all roles part of the research team? Or is it just some roles?

 

[newbieoxford]

N.B. = An abbreviation for the Latin phrase nota bene, meaning “note well.” It is used to emphasise an important point.

All roles are part of the research team! If you have a role, you're part of the team.

 

[plog]

Wow, you sure like to go out of your way to make your messages unclear. Not only was it an abbreviation, it was an abbreviation for a phrase not even in the language we were communicating in. But hey, you sure showed everyone how smart you were by kind of using Latin.

Here's the SQL to get unique names of everyone on your research team:

SELECT [Name]
FROM TableContacts
INNER JOIN Contact_Role ON TableContacts.ContactID = Contact_Role=ContactID
GROUP BY [Name]

 

[newbieoxford]

N.B. is (I thought) a commonly used and understood Latin-derived term, like e.g. and i.e. So many languages and confusions... thanks for your patience with me
I got rather excited about your piece of code but it returned a 'JOIN expression not supported' message, alas.

For transparency, I posted this question in accessforums. I've tried to post the link to the post here but it says I can't post links until my post county is more than 10 and it isn't yet, argh.
Haven't yet found a solution but someone has suggested using CONCAT function and I'm trying to work out how to do that.

Thanks to everyone for your input and time

 

[plog]

The only reason I can think of for that message is that your ContactID fields are different data types (one numeric, one text). Is that the case?

 

[newbieoxford]

The only reason I can think of for that message is that your ContactID fields are different data types (one numeric, one text). Is that the case?

Nope, all the ID fields are numbers

 

[CJ_London]

have you tried the concatrelated function suggested in the other thread?

 

[newbieoxford]

have you tried the concatrelated function suggested in the other thread?

I looked into it but too complicated for my non-programmer brain

I've just created a new 'Yes/no' field for whether they are in the research team or not, and gone through and checked each person manually. A rather basic and luddite solution but it does the job.

I will mark this thread as resolved (poorly!).