Multivalued field

[zezo2021]

Hello friends

How can I save the Multivalued field to the normal field string?
for example the value

1,2,3

can be normal string 1,2,2 without dropdown or anything related to a multivalue field

Thank you

 

[MajP]

Not sure what you mean. Would you like to do the same thing as a MVF without using a MVF? Obviously you can simply store the string "1,2,3", but then you really cannot do anything with it easy. Here is an example of a fake MVF that uses a normalized junction table.

Faking a Multi Value Field

I have seen a few posts lately on people wanting to use multi valued fields. Most Access developers steer away from them and build their own related tables. Although they are properly normalized and work well, they can get very confusing even if you know the ins and outs. I wanted to see if I...

www.access-programmers.co.uk

You can do something like that where you store your values in a traditional table and then use the concatenate to display them.

 

[zezo2021]

Hello
example
I have a field containing multivalue
for example
category (multivalue)
1,2,3
5,6,3

I want to convert these value to short text field

category (multivalue) Cat(ShortText)
1,2,3 1,2,3
5,6,3 5,6,3

 

[MajP]

If I understand you correctly you have a MVF (lets call it 'MVF_Values') and now you have created a new text field in the same table (lets call it 'ConcatValues'). You want to take the MVF values and make a string with them and store it.
I can do this and have the code, but I am out this weekend for a wedding. If someone does not get back I can get to you by Sunday.
Basically
1. You will the recordset of your table.
2. For each record with the MVF you will get a second recordset. The value of the MVF field is a second recordset with the MVF values
3. You will loop the second recordset doing a simple concatenation.
4. You will do an insert query of the concatenated string into your new text field

See close example

Manipulate multivalued fields with DAO

Office VBA reference topic

docs.microsoft.com

 

[theDBguy]

Hi. Perhaps this simple function could be useful.

Code Snippets

 

[zezo2021]

Example

 

[Mike Krailo]

Here is your example with slightly modified names to make it easier to read and a working query.

 

[zezo2021]

 

[zezo2021]

Here is your example with slightly modified names to make it easier to read and a working query.

 

[Pat Hartman]

I know this is what you asked for but it is the worst possible solution. I would rather see you stay with the MVF and learn the necessary SQL syntax to control it than mush the values together. Perhaps you'll come to that conclusion yourself once you try to do anything with the mushed data.

 

[keviny04]

The OP probably didn't like MVF and wanted to switch. I've found MVF to be most useful when I need a "choose all that apply" type of user input. A MVF is shown as a drop-down list of checkboxes, which is easy for the user to operate and to understand its purpose. For instance, a search form may allow the user to enter multiple search criteria. The user checks the choices on the drop-down list, and a query can be easily made for the selected choices by using a Recordset loop to construct a comma-separated list of the selected items, and then using the SQL:

SELECT * FROM MyTable WHERE field1 IN ( comma-separated list of user's choices );

Of course, MVFs are frowned upon because they are not "atomic" (I think that's the term) data required by traditional relational databases.

 

[Pat Hartman]

The OP probably didn't like MVF and wanted to switch.

But the solution is NOT to mush a bunch of values into a single field. When you have more than one value you have many and many REQUIRES a separate table.

Of course, MVFs are frowned upon because they are not "atomic" (I think that's the term) data required by traditional relational databases.

The MVF uses a separate table to store the data correctly so it is "atomic". Experts object because the properly normalized table is hidden and therefore you have to use special SQL to access the data.