Solved Convert/extract multivalue field to one value field (1 Viewer)

alvingenius

IT Specialist
Local time
Today, 11:53
Joined
Jul 10, 2016
Messages
169
Hello
I've a table with a multivalue field and i wanna extract it into one value fields because there's other fields related to specific value on this multivalue field

to show you what i mean i've this primary table with data and multivalue field
1639833453000.png


So, for example: Pop have English , French and Spanish exams at 2021, and i wanna put a result of every exam pop have and a note regarding this exam !!
like this
1639833511473.png


doin this in query, in the Pass colume if i checked the first row, it will check the both 3 records of Pop and that's not right! and i know it wont work like that.

So, i think it needs to create another table linked somehow to the primary table, but how to make it work ?

PS: i need this new table to be updateable, so if pop want to added another exam in primary table like: Chinese besides (En/Fr/Sp) in 2021, i can see this record in the new table

i've attached the example database if u wanna check it

Thanks for your help 🌹
 

Attachments

  • Example.accdb
    460 KB · Views: 295

arnelgp

..forever waiting... waiting for jellybean!
Local time
Today, 17:53
Joined
May 7, 2009
Messages
19,169
i added a table tbl_Exams and use it as lookup for your table.
also notice that the exam field on the orig table was changed to long integer.
i then add another table (junction) tbl_Exampass.
next i created a query, query1 using the 3 tables.
then i created a form (datasheet) for query1.
i add code to the load event to add the ids, and exam from
primary table to the junction table.
note that i also have index (no dups) on the junction table (primarytblID + exam).

open the form, query1.
 

Attachments

  • Example (2).accdb
    884 KB · Views: 355

alvingenius

IT Specialist
Local time
Today, 11:53
Joined
Jul 10, 2016
Messages
169
i added a table tbl_Exams and use it as lookup for your table.
also notice that the exam field on the orig table was changed to long integer.
i then add another table (junction) tbl_Exampass.
next i created a query, query1 using the 3 tables.
then i created a form (datasheet) for query1.
i add code to the load event to add the ids, and exam from
primary table to the junction table.
note that i also have index (no dups) on the junction table (primarytblID + exam).

open the form, query1.

that's a great solution u provided like always, thanks a lot
but it's conflicting with my PS
i need this new table to be updateable, so if pop want to added another exam in primary table like: Chinese besides (En/Fr/Sp) in 2021, i can see this record in the new table
since we used append query, every time when we open the form, it will append the new records
is there another way to get this new table without using an append query?
 

theDBguy

I’m here to help
Staff member
Local time
Today, 02:53
Joined
Oct 29, 2018
Messages
21,357
Hi. If you need to split up the MVF column so you can modify each one individually, then I think you might as well not use an MVF at all and just use a normal junction table. You can always display the exams to look like a MVF using record concatenation.

 

alvingenius

IT Specialist
Local time
Today, 11:53
Joined
Jul 10, 2016
Messages
169
Hi. If you need to split up the MVF column so you can modify each one individually, then I think you might as well not use an MVF at all and just use a normal junction table. You can always display the exams to look like a MVF using record concatenation.

Thanks for the suggestion
but i need mvf because theres alot of reports depends and i need to see it the way mvf displays it.
 

alvingenius

IT Specialist
Local time
Today, 11:53
Joined
Jul 10, 2016
Messages
169
As I said earlier, displaying the data as MVF wouldn't be a problem. Now, it's just a matter of what this part means.

Couldn't the reports also depend on a regular table instead of MVF?
Well, i can do it using one value field
but i have data entry forms
with combo box to choose between 20 values
and the best way to do it is using a combo box with multivalue field, since nothing else was required and i have already built the whole system With the forms and reports with MVF.
but now a new requirement appeared couple days ago, is to put another value to every record on the multi-value field like the example shown above.

@arnelgp solution is totally valid but its not gonna work well with my project as it require Updates on the primary table!

and your solution is valid too and i didnt use it in the first place on this project since the requirements at that time is working great with MVF

so i’m looking for another solution that can do this task without sacrificing of not doing updates on the primary table
 

gemma-the-husky

Super Moderator
Staff member
Local time
Today, 09:53
Joined
Sep 12, 2006
Messages
15,613
The trouble is that behind the scenes Access deals with the MVF by using a second sub table.

What you will have to do is achieve the same thing manually and use your own solution instead of the inbuilt solution. The upside is you get more control than the MVF gives you .So you can't get away without redesigning the aspects of your system that use the MVF, and that affects the way you use the primary table - well anything related to the MVF. This is why most of us wouldn't touch MVFs at all. It's not hard to design it properly in the first place, but redesigning a built system to a different paradigm can be much more awkward.
 
Last edited:

mike60smart

Registered User.
Local time
Today, 09:53
Joined
Aug 6, 2017
Messages
1,899
I would agree with DBG and Dave and redesign and not use MVFs at all.
 

theDBguy

I’m here to help
Staff member
Local time
Today, 02:53
Joined
Oct 29, 2018
Messages
21,357
Well, i can do it using one value field
but i have data entry forms
with combo box to choose between 20 values
and the best way to do it is using a combo box with multivalue field, since nothing else was required and i have already built the whole system With the forms and reports with MVF.
but now a new requirement appeared couple days ago, is to put another value to every record on the multi-value field like the example shown above.

@arnelgp solution is totally valid but its not gonna work well with my project as it require Updates on the primary table!

and your solution is valid too and i didnt use it in the first place on this project since the requirements at that time is working great with MVF

so i’m looking for another solution that can do this task without sacrificing of not doing updates on the primary table
Hi. Thanks for the additional information. I haven't looked at @arnelgp's solution; but if you want to have both the convenience of using a MVF and the ability to add individual data to them, then I would suggest you keep the MVF in the main table; but also, create a junction table to duplicate the items frim the MVF as separate records. You will need some VBA to keep them synchronized.
 

Pat Hartman

Super Moderator
Staff member
Local time
Today, 05:53
Joined
Feb 19, 2002
Messages
42,970
I looked at arnel's solution and it is a bandaid. It duplicates the MVF field by making a new table and of course there is no way to ensure that will happen going forward. The MVF and the new table are not related so this is an accident waiting to happen.

let me join the rest, get rid of the Multi, value fields. You can use a small subform or the concat() function (links in multiple threads) to make the courses look like a coma separated string.
 

arnelgp

..forever waiting... waiting for jellybean!
Local time
Today, 17:53
Joined
May 7, 2009
Messages
19,169
The junction table will be appended everytime you open the form to ensure there is a related record to it. Dont worry about duplicate or that previous record being overwritten, i made a compound index set to No Duplicate. See the table in design view.
 

alvingenius

IT Specialist
Local time
Today, 11:53
Joined
Jul 10, 2016
Messages
169
Ok, I've heard you all and convinced to not use MVF
I'll use a junction table
but what is the best practice to do it?

i've this lookup table named: conentions
1639944196274.png


and another lookup table named: countries
1639944233405.png


so the junction table that it will be a data entry using a form with these fields
1639944697036.png


so, every country can apply to these 19 conventions, for a year they can apply for a couple of conventions

so in form the user will choose the country name from a combo box, and then should choose what conventions that this country applied too and in what year

So. what im asking here, what's the best senario for the form to let the user choose between 19 conventions ? and how to save em in the junction box ?
i'm attaching the full example
 

Attachments

  • Example2.accdb
    464 KB · Views: 297

Pat Hartman

Super Moderator
Staff member
Local time
Today, 05:53
Joined
Feb 19, 2002
Messages
42,970
Here's a many-many sample database. It shows how to access the data from either side of the relationship using two methodologies. One uses a pop up form to show the related data and from the other side, the form uses a subform to show the related data. Both make sense so use whichever method you prefer.
 

Attachments

  • ManyToMany20210414.zip
    1.5 MB · Views: 326

mike60smart

Registered User.
Local time
Today, 09:53
Joined
Aug 6, 2017
Messages
1,899
Hi
Here is your database modified to cater for the process of selecting a Country and specifying the Convention(s) & Years.
 

Attachments

  • Example2.zip
    35.3 KB · Views: 353

Pat Hartman

Super Moderator
Staff member
Local time
Today, 05:53
Joined
Feb 19, 2002
Messages
42,970
@Cotswold It was very kind of you to fix the database but I don't like the subform navigation bar being hidden. When you have nested forms, you can name the navigation bars to avoid confusion. That was my personal contribution to Access 2007:)
 

Users who are viewing this thread

Top Bottom