Multi-value field with Pivot Table View

[martixz]

Hello guys. I have a little but quite strange problem using MS Access 2010. One of my database field's is using multiply values (you can add one than more values in that field by checkbox). So, for example, one record in that ONE field looks like this: "Gastropoda; Mermithidae; Nematoda; Oligochaeta; Scorpiones". When I'm going to "PrivotTable view" I have only "Ga" for instance instead of full names...



I don't know if my description of the problem is clear because my English is not very fluent but I really need your help solving it. The essence of the problem is the fact that I'm using MS Publisher related with the database and as I mentioned in the Publisher fields I can see only the beginnings of the full fieldd, "Ga" for example instead of "Gastropoda; Mermithidae;....".

I would be very thankful if anyone can help me.

Here are some screens where the problem can be seen.

[June7]

That is not strange and is to be expected with multi-value field and lookup with an alias.

Number 1, many developers consider multi-value fields poor database design (I NEVER use). Access is the only relational database that has them.

Number 2, setting lookups with alias in table is also often viewed as poor design. http://access.mvps.org/access/lookupfields.htm

However, here is a contrary view http://www.utteraccess.com/forum/Mul...-t1991337.html

If you must do both then you need better understanding of what's really going on. Sounds like what is saved in the multi-value field is an ID like 'Ga', not the full descriptor 'Gastropoda'. I don't know if PivotTable view can deal with multi-value field. Building query that uses multi-value field involves expanding the multi-value field. Review: http://office.microsoft.com/en-us/ac...010149297.aspx

[martixz]

I have made that field much larger and now something have changed... Instead of "Ac" now it is like that in the screen. The problem remains.

[martixz]

If someone can help me, there are files of test database and publisher documment. http://speedy.sh/YvGtb/DB.rar

[June7]

Apparently MS Publisher doesn't like the multi-value field. Multi-value field will cause problem anytime referenced by an app outside Access. They only solution I can see is VBA code to expand the multi-value field and concatenate the discrete values into a single string.

[martixz]

Hmm, maybe I would never use multiply values but its specific situation because information is being inserted using a form. That problematic field contains a list of values (sometimes it is selected one value, sometimes more). I don't know how to realize that situation in MS Access in a different way...

[June7]

I described solution in previous post. Use VBA. Review: http://allenbrowne.com/func-concat.html

[martixz]

Maybe someone could help realize this chatting on skype? I never used something like that in MS Access before, so its a bit difficult to understand how it should work in my situation.

[June7]

Maybe someone but I don't. You haven't used VBA before? Now is time to learn. I can't download your file right now but I can't open rar files anyway.

[martixz]

I never used VBA before and I think I would need it only for this situation if it is the only way to fix the problem. If it is easy to fix the problem, won't take too much time for you and someone could help me, my skype id: martixz

[June7]

I don't do skype for forums and I can't work with rar file. Provide zip or uncompressed database.

[martixz]

http://speedy.sh/FW8qe/Certificate-10-109.pub

http://www.speedyshare.com/qhc82/DB-40.accdb

[martixz]

I have found a simple solution to this problem. I have tried to use MS Office 2013 and everything works correctly! I think, Microsoft have noticed this error of multiply values and fixed it in 2013 version. By the way, June7, thank you for your help and advices.

[June7]

Glad you found solution. I have not worked with 2013 apps yet.