Error: The multi-valued field cannot be used in a UNION query

[jen_access_queenornot]

Hi everyone,

I am working on a database with my team and we have three tables with three types of data. They're all pretty much made in the same way with different "themes" and we fill them out thanks to three different forms.
I want to extract the 3 first columns of these three different tables which are "DATE", "NAME", "ACTION(S)" and combine them in a single table (or query).
For context, a person NAME on a given DATE can do up to 3 ACTIONS (multi-valued field) and each table has different actions. I would like to have a table that summarise all the actions someone has done on a given day for example.

Does that still make sense up to here?

If so, here is my problem: I created three queries to extract these 3 columns but when I try to UNION them I get the following message "the multi-valued field cannot be used in a UNION query".

Is there a way to UNION these fields? All the threads I'm finding here seem to mention Attachments or things like that but I can't find an easy fix for this problem.

Thanks in advance!

 

[Ranman256]

Q1,select top 3 table1...
Q2, Select top 3 table2...
Q3, Select top 3 table3...

Q4 union:
select * from Q1
union
select * from Q2
union
select * from Q3

 

[theDBguy]

You will have to break down each action into separate rows, so you can use them in a union query.

Try something like:

SELECT [Date], [Name], Action.Value FROM TableName

 

[The_Doc_Man]

This sort of problem is exactly why we do not recommend use of a multi-valued field (MVF). The reason the UNION cannot deal with it is because Access wants to make a single-valued row and the nature of the MVF is... multi-valued. In the long run, you would do better to do that differently by turning your multi-valued field options into a child table and then perform a JOIN between parent and child to extract the value. I know you didn't want to hear that, but we try to tell you the truth here. An MVF just gets in the way more often than not.

 

[jen_access_queenornot]

Thank you everyone, I am going to try these and hope for the best, breaking everything down seems the logical solution, I don't know why I did not even think of that! Thanks!

@The_Doc_Man I completely understand and if I could re-do it I'd probably follow your advice as it is not the first time I'm facing this kind of issue. I find the MVF a bit more user friendly on the forms though, and for people who don't know anything about Access (aka the rest of my team) I find it complicated to change the way we record data now (after a couple of years of use already).

 

[ebs17]

Does that still make sense up to here?

Three tables with the same columns could also be understood as missing a normalization step.
The necessity to use UNION is in very many cases an indication of this.

 

[The_Doc_Man]

Thank you everyone, I am going to try these and hope for the best, breaking everything down seems the logical solution, I don't know why I did not even think of that! Thanks!

@The_Doc_Man I completely understand and if I could re-do it I'd probably follow your advice as it is not the first time I'm facing this kind of issue. I find the MVF a bit more user friendly on the forms though, and for people who don't know anything about Access (aka the rest of my team) I find it complicated to change the way we record data now (after a couple of years of use already).

The correct way to replace an MVF is to use a parent/child table combination - which is actually what an MVF is, except that the child table is hidden. If it is instead made explicit, you can use a standard combo box on a form and in queries you can use a JOIN to the child table to fetch the "extended" field. It is the hiding implicit in the MVF that causes the big problems down the road.