Union query only returning 255 characters

[ajetrumpet]

Jim,

You must have run the queries incorrectly from what I posted. But I'm sorry, mine had a union, whereas this one has a 'union all'. this works and does not truncate:

select [Order] as Sequence, Question, ""as Response,"" as RespOrder,"" as tblQuestResponseSets_SHOW, "" as tblQuestResponseSets_HIDE from tblTempCounsel


UNION ALL select [Order],"",Response, resporder, tblQuestResponseSets_SHOW, tblQuestResponseSets_HIDE from tblTempCounsel

As you can see there, I have eliminated the 'group by', 'order by' AND the 'first()' function. that I think is the first step. My guess is that Allen Browne's page is correct...the first() function requires aggregation and that's probably what's doing it.

[jpkeller55]

Thanks Adam...you are right. This query is not truncating. But, the order is not right. How do I get this to order correctly? Do I need to add some other field in the table to get this to sort in the correct order?

[ajetrumpet]

Thanks Adam...you are right. This query is not truncating. But, the order is not right. How do I get this to order correctly? Do I need to add some other field in the table to get this to sort in the correct order?

What I would do if I were you jim, is try the query with one issue ata time added back in. I already listed what I took out. The 3 things. I would start by adding the order by clause back in as that probably won't cause any problems. 1 thing to watch out for if its only in 1 of the select queries is that it might just order those and not the ones coming from the other select. You'll know though when you see the output. Does that make sense?

[jpkeller55]

Yes, it makes sense. I will try and mess with it. It is too bad, because the original query worked fine...just truncated. Your version does not truncate, but it also repeats the question a number of times and is out of order. If I could put these two together, I would have the answer. Thanks for trying.

[jpkeller55]

It is definitely the "Group By" that is causing the truncation. I don't see any way around this. As soon as I remove the "Group By" and modify the query accordingly, the questions and responses are not grouped correctly and the record is repeated multiple times. I am thinking the must be another way to return the records in the format that I need that will also not truncate the data. If anybody knows, I am all ears. Thanks, Jim

[ajetrumpet]

Jim,

why don't you simply use the group by clause, order by clause, and the first() function, if that's still important to you, in separate queries on their own. at that point all of the data will be the way you want, they will just be in query sets instead of table sets, which is absolutely the same in terms of structure schema. Once you have all of that done, run the union on the two queries that are the result of all the work. it's that simple.

yes, you will have 3 layers of stacked queries, but...is this bad for the program? NO. is it commonplace to do this? YES. sometimes this method is necessary because JET just doesn't have the capability to handle this particular request...

[jpkeller55]

That sounds good to me...I will see if I can figure out how to do that. May need some help but will give it a shot.

[ajetrumpet]

JIM,

Apparently you cannot have a GROUP BY anywhere that is related to a union query. I took your group by out of the level 1 stacked query because it was AGAIN causing the truncation of the level 2 union.

I think this is completely ridiculous, but that's the way Access works I guess!

So...you cannot have a group by 'at all' in any of the 3 queries used in this example. Take care!

[ajetrumpet]

Jim,

I guess this one wasn't right either. ugh...

OK, I don't think you can have a group by in anything related to a Union, so what I would do if I were you is just take QueryA and make it a MAKE TABLE query. Put the GROUP BY clause back in it because THAT is what was outputting less records, which is the way you want it to look. Since the group by cannot in any be associated with the union query, at this point I'm thinking you have to get it out of there and the only way to do that is to make a table out of it.

so do that, and then query the new table AND query number 2, and use both of those in the UNION ALL query in my example. that should work fine. so in essence, using stacked datasets you will have:

level 1= the 'make table' query WITH the group by clause used to make it. AND ... query B.

level 2 = the union all query that is already in the example.

I'm sorry man, but I don't work with unions that much and apparently they are a pain in the rear to work with. I hope you get it straitened out. I apologize for not looking at it before uploading. Been a long day. If you get stuck again, I'd be glad to help you out. I'm takin off for the night. talk to ya later![/QUOTE]

[jpkeller55]

Adam,

This is working for me. I have followed your advice and am running a make table query which the first part of the union query uses and a query for the second part. It was a bit complicated, but I have VBA code written to call these queries in order and it is working as intended. Thanks so much for your help!

[jpkeller55]

Attached is the final answer with a form to illustrate the VBA code used to run the queries.