How to avoid repetition of records in query for multi value field

[accessdummy]

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.

[CJ_London]

Think all you can do is not include the multivalue field in your query.

Or if the role is related to the research team (i.e. in this team the role is nationalpartner, in that team the role is international partner) then modify your query to reflect this. Otherwise if you want to avoid duplicate rows but need a role to appear, on what basis do you decide which role to use?

Another option is to use the concatrelated function here http://allenbrowne.com/func-concat.html

Its one of the limitations of using multivalue fields

[JoeM]

There are a few different ways:

1. Only return the Person's name in the query (you can include the Role in the criteria, but elect not to show it).
Then, switch the query to SQL View, and add the word "DISTINCT" and "SELECT", so the first line starts out like this:
SELECT DISTINCT NameField ...

2. Change it to an Aggregate Query by clicking on the Totals button (looks like a Sigma).
This will add a Totals Row with the phrase "Group By" under each field.
Under the Roles field, change "Group By" to "First".
Then will only return each person once, showing just the first Role it finds for that person.

By the way, many users despise the use of Multi-Valued fields (myself included), and would recommend not using them. See the following for discussions on the topic and alternatives:
http://stackoverflow.com/questions/1...ds-a-good-idea
http://stackoverflow.com/questions/1...s-in-ms-access

[accessdummy]

Thanks JoeM! Tried both and failed:
1. When I add 'DISTINCT' in SQL view I get the message 'The DISTINCT keyword cannot be used with the multi-valued field 'Contacts.Role'
2. I get a box 'Enter Parameter value' Expr 1030

Oh dear, perhaps I will have to create a junction table and many to many relationship? I did read about widespread dislike of the multi value fields. Just seemed the much easier option.

[accessdummy]

Hi Ajax, thanks for your response.
Associated with the research team are 14 roles. Some people have 2 or 3 roles. So what I'd ideally like a query to return is the list of every person in the research team, then in role field have the different roles assigned to this person, e.g.

Joe Bloggs; World Bank; joe.bloggs@worldbank.org; NationalPartner, AdvisoryPanel, PrincipalInvestigator
Camilia Jones; Newscastle University; c.jones@newcastle.ac.uk; Researcher, ScienceBoard

[Micron]

You might solve this by using your critera on the .value property of the multi value field. Looks like WHERE [contacts].[roles].value = "research"....

[JoeM]

Thanks JoeM! Tried both and failed:
1. When I add 'DISTINCT' in SQL view I get the message 'The DISTINCT keyword cannot be used with the multi-valued field 'Contacts.Role'

Another good reason why not to use multi-valued fields, I guess.
I am not intimately familiar with all the pitfalls of multi-valued that answer because I don't use them. I thought maybe you could get away with using the same methods as you would with other fields, but apparently not!

[CJ_London]

Hi Ajax, thanks for your response.

so did you look at the link I provided? which should enable you to show yopu data as

Joe Bloggs; World Bank; joe.bloggs@worldbank.org; NationalPartner, AdvisoryPanel, PrincipalInvestigator
Camilia Jones; Newscastle University; c.jones@newcastle.ac.uk; Researcher, ScienceBoard

[accessdummy]

Hi all, I'm still struggling with this.


People will be happy to hear I’ve killed the multi-value field and created a junction table
But I still have the same problem. Let me explain with some sample data.

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 (these represent all the different roles). 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?

[orange]

I agree with the others re MVF -- glad you got rid of it.
As for your duplicates, it really comes down to your definition of a duplicate.

If John can have 3 roles, then there are 3 records for John if you're trying to identify roles for John.

As Ajax mentioned you can use func-concat and put the 3 roles beside John's name.

Good luck with your project.

[CJ_London]

think you will find this link of interest - the identical question

http://www.access-programmers.co.uk/...=1#post1513587

to avoid confusion please read this http://www.excelguru.ca/content.php?184

[accessdummy]

Hi Ajax. Thanks for the link re cross-posting, I wan't aware of the protocol. Do you suggest I delete one thread?
I've looked at the concatenate function page, it seems like a great solution, but I don't understand the instructions, I think it's beyond my capabilities :-(

[CJ_London]

no need to delete the other thread - the point is to advise people when you are doing it so they don't spend time providing the same answers. If you get to be known as a persistent cross poster who does not follow this simple requirement you will find people will stop responding.

with regards the concat function, all you need to do is copy and paste the code into a new module, then use it in your query a bit like a domain function.

[accessdummy]

Hey y'all. The concat function was too complicated for my non-programmer brain

So 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!).

Thanks

[moligane]

OK HERE's a simple solution to MultiValued (MV) field returning duplicate rows except the MV field :


1) Assume that you have a table and with two columns called table1.ID, table1.country. The attribute of table1.country is set to: "ALLOW MULTIPLE VALUES = YES" in the table design.


2) Create a single row with the following values:


ID = 1
country = USA, UK, Philippines


3) Select ID, country.value from table1


returns the following:


1, USA
1, UK
1, Philippines




4) Select ID, country from table1


returns the following


1, "USA, UK, Philippines"




I was looking for a solution of a similar issue posted in this forum and the suggested work-around are possible but complicated. I analyzed the SQL syntax, I saw the xxxxx.value appended only in the multivalued columns. I removed the value and access treated it as single valued column. I could not find that in MS documentation. I registered as a member just to post this solution, I hope it works for others.