Solved Convert/extract multivalue field to one value field

[alvingenius]

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

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

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

 

[arnelgp]

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.

 

[alvingenius]

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]

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.

Code Snippets

 

[alvingenius]

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.

Code Snippets

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.

 

[theDBguy]

i need to see it the way mvf displays it.

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.

because theres alot of reports depends

Couldn't the reports also depend on a regular table instead of MVF?

 

[alvingenius]

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]

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.

 

[mike60smart]

I would agree with DBG and Dave and redesign and not use MVFs at all.

 

[theDBguy]

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.

 

[isladogs]

For more info on why using MVFs is a bad idea, see
Multivalued Fields - Mendip Data Systems (isladogs.co.uk)

 

[Pat Hartman]

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]

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]

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

and another lookup table named: countries

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

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

 

[Pat Hartman]

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.

 

[mike60smart]

Hi
Here is your database modified to cater for the process of selecting a Country and specifying the Convention(s) & Years.

 

[Pat Hartman]

@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

 

[alvingenius]

Well, thanks @Pat Hartman and @mike60smart, and thanks @theDBguy too
I've used @mike60smart way in the example file he attached, and regarding Concatenate records, I'm using this function

 

[theDBguy]

Well, thanks @Pat Hartman and @mike60smart, and thanks @theDBguy too
I've used @mike60smart way in the example file he attached, and regarding Concatenate records, I'm using this function

Hi. Glad to hear you got it sorted out. Good luck with your project.