multivalued field and insert into / append

[honza7632]

Hi,

I work with access 2007. While improveing older version of database, i made in two tables a multivalued field. It seemed as a good idea, but than I realized, that append query can not work with this type of field.
Do you know any way to do it - to append records containg multivalued field? I am not much skilled in programming. The only way how to do that seems to change structure of database (new table in 1:N relation), but I see it as the last option - multivalued field is more user friendly.

Thanks

 

[jzwp22]

Welcome to the forum!

You can do an append query but it is limited to a specific type as discussed in this article from Microsoft. With the convenience of multivalue fields comes some limitations. As far as I am aware, there are no VBA code work arounds either.

I generally do not use the multivalue fields but go with a traditional database design. Using a traditional database design, you can create a simple function that would display the many values in a single field within a query thus providing a similar outcome as the multivalue field without the other limitations.

 

[honza7632]

Thanks, it seems i will have to leave idea of multivalue field. I have tried, but not succeeded, to create a query result containing all values in one field. I have idea of something like this:

query result obtained:
id values
1 a
1 b
2 a
2 b

query result demand:
id values
1 a; b
2 a; b

is possible to do it that way?

Thanks for answering

 

[vbaInet]

It is possible:

http://allenbrowne.com/func-concat.html

NB: Yes, you can use VBA to manipulate a multivalued field. This is what Recordset2 is mainly for. But it's too much hassle and not worth it, and besides that, it's not portable.

 

[honza7632]

It was hard, many fails...
but finally I succeeded, with your help.

Thanks a lot!

 

[vbaInet]

The fact that you struggled to get it working can only mean you will never forget how to do it next time

Glad to hear you found a better structure.