Multi-Field Union Query

[XelaIrodavlas]

Hi all,

I have two tables containing (let's say for simplicity) questions and attachments (pictures). I am trying to perform a union query to join all the questions and pictures into one report, but it won't let me union the attachment because 'the multi-valued field 'TableA.Pictures' cannot be used in a union query'.

I have searched and searched for a solution (and got kind of close) but i still can't get it to work. The best I can do is union everything like below, which gives all the questions as desired, but says #Error in the pictures column:

SELECT TableA.*
FROM TableA
Union
SELECT TableB.*
FROM TableB;

(Note tables A and B have the same structure, several yes/no and open text questions as well as one attachment field. )

Does anyone know a solution? Or can someone explain how the other guy's solution works (see the link), because he sure seemed happy about it while I'm still doing this against my keyboard ->:banghead:

Thanks in advance,

 

[Brianwarnock]

I notice that he has two subqueries which are unioned, they do not include the attachments, he selects the data from the union and adds in the attachment left joining the union query to the table on id. The original data is in one table.

Brian

 

[XelaIrodavlas]

SELECT u.*, a.Attach
FROM (
SELECT
qry1.QuestionID,
qry1.Question,
tblAnswers.AnswerID,
tblAnswers.Answer
FROM qry1
LEFT JOIN tblAnswers
ON qry1.QuestionID = tblAnswers.QuestionID
UNION
SELECT
qry2.QuestionID,
qry2.Question,
"" as AnswerID,
"" as Answer
FROM qry2
) AS u
LEFT JOIN tblQuestions a
ON u.QuestionID = a.QuestionID;

hmm so in this example he union'd all the non-attachment fields via two new queries, giving all the question fields as needed, then joined that to the original questions table where the ID was the same...

I see why this wasn't going to help me now, both of his queries came from the same original table, he just separated them to choose random questions... Since my tables are separate my attachments are stored in separate places so I can't use this code to union them. I'd need a way to tell it which question ID links to which attachment from which table - and the ID's can potentially be the same.

Thank you very much for helping me get my head round this, but I'm still lost

 

[Brianwarnock]

Ok a wild stab , I think that you need to do your union query selecting all fields except the attachments , then run two more queries to add the attachments as they are in two tables, but this is not only all new to me but I can't test as I can't handle multivalue fields on my back level system.

Brian

Edit just noticed your post, guess you have a problem with the ids. Do any other fields have unique values?

 

[XelaIrodavlas]

Potentially - if I concatenate the ID's with another field it could be used as a makeshift ID. This could take some thinking, I'll have to play around a bit...

Cheers for the advice,

 

[Brianwarnock]

I was wondering if the two sets of questions were different


Brian

 

[MSAccessRookie]

As Brian Pointed out, perhaps the two sets of Fields are different. Remember that in a UNION Query, the individual Queries have the same number of Fields, and they must be the same Type of Data and also must be in the the Same order. Check the Datasets and make sure that they represent exactly the same thing. In addition, it helps for the related Fields to all have the same Name, and I use Aliases to accomplish this whenever I need to.

 

[Roon3y]

@OP were you able to get any solution? I am stuck on the same boat.

 

[MSAccessRookie]

@OP were you able to get any solution? I am stuck on the same boat.

Roon,

Feel free to open a new Thread regarding your specific issue. Remember to include all relevant information so that we can determine the best way to assist you.

-- Rookie