Reporting multivalued field in report (1 Viewer)

SteveAB1

New member
Local time
Today, 08:36
Joined
Mar 12, 2021
Messages
3
Greetings
I'm using Access 2016. I have a report (not using any VB, just building a report) in which I would like to display an individual's name along with all of that individual's qualifications (always at least one, and typically no more than three). The qualifications are stored in a multivalued field. I am trying to use a Combo-box in the report, but my resulting report displays the individual twice if they have two qualifications, thrice if they have three, etc. It looks something like below:

Joe Smith
MD
Mary Jones
MD
Mary Jones
PhD

I'd like it to be:

Joe Smith
MD
Mary Jones
MD, PhD

Any suggestions?
 

theDBguy

I’m here to help
Staff member
Local time
Today, 05:36
Joined
Oct 29, 2018
Messages
21,357
Can you post the SQL statement for the Record Source of your report? Make sure you don't include the Value field.
 

SteveAB1

New member
Local time
Today, 08:36
Joined
Mar 12, 2021
Messages
3
Thanks for your response.
I will, but I'll need to wait until Monday...it's on my work PC :mad:. I was just playing with that and tried it with Value and without...didn't seem to make a difference.
 

theDBguy

I’m here to help
Staff member
Local time
Today, 05:36
Joined
Oct 29, 2018
Messages
21,357
Thanks for your response.
I will, but I'll need to wait until Monday...it's on my work PC :mad:. I was just playing with that and tried it with Value and without...didn't seem to make a difference.
You should be able to tell the difference by simply running the query. When you include the Value field, the query result should show more records than if you don't include the Value field.
 

arnelgp

..forever waiting... waiting for jellybean!
Local time
Today, 20:36
Joined
May 7, 2009
Messages
19,169
see Qeuery2 report.
 

Attachments

  • MVFReport.accdb
    672 KB · Views: 208

SteveAB1

New member
Local time
Today, 08:36
Joined
Mar 12, 2021
Messages
3
And...it's Monday

arnelgp, Thank you so much for the db with the solution. I'm working through the module to adapt the code to my db, but that works great!

I'm surprised that there isn't a "native" solution in Access (i.e., one that doesn't require a chunk of custom code). My tables are constructed the same as what arnelgp provided in the MVFReport db, with "Role" below being the MVF.

The SQL below is what yields a report as described in my first post.

Here's the SQL for the Record Source:
SELECT [Consultant Information].Code, [Consultant Information].Last, [Consultant Information].First, [Consultant Information].Cred, [Consultant Information].Specialty, [Jan 15 Internal Roles].Role, [Consultant Information].Phone, [Consultant Information].Unitnumber, [Consultant Information].Area, [Consultant Information].Mon, [Consultant Information].Tues, [Consultant Information].Wed, [Consultant Information].Thurs, [Consultant Information].Fri, [Consultant Information].Active

FROM [Jan 15 Internal Roles] RIGHT JOIN [Consultant Information] ON [Jan 15 Internal Roles].ID = [Consultant Information].Role.Value

WHERE ((([Consultant Information].Active)=Yes))

ORDER BY [Consultant Information].Last;

And this is the Row Source SELECT stmt:
SELECT [Jan 15 Internal Roles].[ID], [Jan 15 Internal Roles].[Role] FROM [Jan 15 Internal Roles];
 

theDBguy

I’m here to help
Staff member
Local time
Today, 05:36
Joined
Oct 29, 2018
Messages
21,357
I'm surprised that there isn't a "native" solution in Access (i.e., one that doesn't require a chunk of custom code).
I haven't looked at Arnel's solution, but I am surprised that you would need any code for this, given that you're already using a MVF in your table. Are you able to post a sample copy of your db with test data?
 

Users who are viewing this thread

Top Bottom