Attachement field in UNION query not possible ??

[Yoosetsu]

Hi All,

I've got a question databank in Access 2007 and some questions have little drawings with it. Those drawings are stored (linked) in a field of the 'attachement' type. (I need to do this, because the OLE-object field does not display .jpg files).

There are different types of questions (say A-questions and B-questions,...) and I have to select randomly a few questions per type. Those queries work perfectly and can seperately be displayed in a report with the (linked) attachment.

I made some first query's per question type to select randomly the questions and now I'm merging them into one selection with UNION.
Now Access is giving me a window with :
"The multi-valued field 'Tablename.Fieldname' cannot be used in a UNION query"

When I delete the attachement field out of the unions, it works perfectly, but the report hasn't the needed drawings with it...
How can I get the drawings of the randomly selected questions in my report ?
Please don't tell me I did this all for nothing (I got already 100's of questions in the databank) and help me out

Thanks a LOT in advance,

 

[vbaInet]

In a query, with multi-valued fields you need to use:

tablename.fieldname.value = 'something'

I'm not so sure about using it in a union query because I don't use multi-valued fields. Alot of people don't to be honest. But it should work.

 

[MSAccessRookie]

Hi All,

I've got a question databank in Access 2007 and some questions have little drawings with it. Those drawings are stored (linked) in a field of the 'attachement' type. (I need to do this, because the OLE-object field does not display .jpg files).

There are different types of questions (say A-questions and B-questions,...) and I have to select randomly a few questions per type. Those queries work perfectly and can seperately be displayed in a report with the (linked) attachment.

I made some first query's per question type to select randomly the questions and now I'm merging them into one selection with UNION.
Now Access is giving me a window with :
"The multi-valued field 'Tablename.Fieldname' cannot be used in a UNION query"

When I delete the attachement field out of the unions, it works perfectly, but the report hasn't the needed drawings with it...
How can I get the drawings of the randomly selected questions in my report ?
Please don't tell me I did this all for nothing (I got already 100's of questions in the databank) and help me out

Thanks a LOT in advance,

I am not aware of anything that would prevent this. Could you possible show us the UNION Query that you are using, so that we have a better idea of your total issue?

 

[Yoosetsu]

I am not aware of anything that would prevent this. Could you possible show us the UNION Query that you are using, so that we have a better idea of your total issue?

Sure, here it is (I simplified the names of the tables and fields, so it gets more easy to read) :


SELECT qry1.QuestionID, qry1.Question, qry1.Attach, tblAnswers.AnswerID, tblAnswers.Answer
FROM qry1 LEFT JOIN tblAnswers ON qry1.QuestionID = tblAnswers.QuestionID
UNION
SELECT qry2.QuestionID, qry2.Question, qry2.Attach, "" as AnswerID, "" as Answer
FROM qry2[FONT=&quot];[/FONT]

If I delete the text in bold, it works perfectly, but I have no drawings, then...
Some extra information : most of the questions are multiple choice, but not all, so some questions don't have stored answers. Therefore, there is a LEFT JOIN to get also the questions that are not multiple choice. I had to add empty fields ("" as answerID) for AnswerID and Answer to be able to UNION.

 

[Banana]

Are the qry1 and qry2 from the same table? If so... would this work?

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;

 

[MSAccessRookie]

Sure, here it is (I simplified the names of the tables and fields, so it gets more easy to read) :


SELECT qry1.QuestionID, qry1.Question, qry1.Attach, tblAnswers.AnswerID, tblAnswers.Answer
FROM qry1 LEFT JOIN tblAnswers ON qry1.QuestionID = tblAnswers.QuestionID
UNION
SELECT qry2.QuestionID, qry2.Question, qry2.Attach, "" as AnswerID, "" as Answer
FROM qry2[FONT=&quot];[/FONT]

If I delete the text in bold, it works perfectly, but I have no drawings, then...
Some extra information : most of the questions are multiple choice, but not all, so some questions don't have stored answers. Therefore, there is a LEFT JOIN to get also the questions that are not multiple choice. I had to add empty fields ("" as answerID) for AnswerID and Answer to be able to UNION.

It looks as if the two Sides in the Union Probably have the same types of data in each Selected Column. Can you post the text for qry1 and qry2? They are each selecting a Column called "Attach", and we will need to verify that both of the Columns contain the same type of data, since the UNION statement will require that they do.

 

[Yoosetsu]

In a query, with multi-valued fields you need to use:

tablename.fieldname.value = 'something'

I'm not so sure about using it in a union query because I don't use multi-valued fields. Alot of people don't to be honest. But it should work.

I tried this, and I changed my "qry1.attach" to "qry1.attach.filedata", but then Access sends the error "Can't use the FileData in the SELECT statement of a Union query"... So, not working this way... unfortunately

 

[Yoosetsu]

Are the qry1 and qry2 from the same table? If so... would this work?

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;

YOU're my HERO !!!!

It worked !! AllRightyRighty! !!! Thx a lot! wheeeehaaaaaa...

 

[Banana]

Awesome. Glad it worked.

If there is a performance issue, the alternative is to look into whether you can write an expression and bind it to the image control so it select the image whenever the row is in view. I've not tried that though so I don't know if that is performant, let alone possible. Just wanted to point out that for the future references.

Best of luck.

 

[Roon3y]

Awesome. Glad it worked.

If there is a performance issue, the alternative is to look into whether you can write an expression and bind it to the image control so it select the image whenever the row is in view. I've not tried that though so I don't know if that is performant, let alone possible. Just wanted to point out that for the future references.

Best of luck.

is this possible for multiple table? I am having this problem, trying to merge all tables in one with the attachments and its returning as Error.

Any idea?