Quick way to stack many columns into one?

[losamfr17]

Hi,

I'm using the following query to stack my columns into one. But I have 73 columns in total in my table. Is there a fast way to do this, or do I have to manually enter the following 73 times?
SELECT Field1
FROM Sheet3
UNION ALL
SELECT Field2
FROM Sheet3;

Thank you.

 

[Joe4]

Well, you could create your code in Excel, and copy it to SQL View of your Access query.
Just place this formula in Excel in cell A1, and copy down to row 220:

=IF(MOD(ROW(),3)=1,"SELECT Field" & (1+INT(ROW()/3)),IF(MOD(ROW(),3)=2,"FROM Sheet3","UNION ALL"))

 

[losamfr17]

Well, you could create your code in Excel, and copy it to SQL View of your Access query.
Just place this formula in Excel in cell A1, and copy down to row 220:

=IF(MOD(ROW(),3)=1,"SELECT Field" & (1+INT(ROW()/3)),IF(MOD(ROW(),3)=2,"FROM Sheet3","UNION ALL"))

That's awesome! However, Access is telling "query is too complex." Any idea why?

Thank you very much!

 

[Joe4]

I have been searching the net, and it appears that there are limits to the number of Union queries you can do, but I cannot seem to find a "hard-and-fast" number as to what it may be.
I did see one thread in which a person changes "UNION ALL" to just "UNION", and it solved the problem of the "query is too complex" error that they were getting.
Try making that change and see if it helps.

 

[losamfr17]

I tried with just UNION, but the same issue occurs. It worked for as many as 50 columns,
Thank you for your help.

 

[Joe4]

Maybe try this:

Split it into two UNION queries, one that does the first 50, and one that does the last 23.
Then, do anothe UNION queries to UNION those first two UNION queries together.
Does that work?

 

[losamfr17]

I tried, it still says query too complex. But I can manage from that point forward.
Thank you very much!

 

[Joe4]

You are welcome.

Note: If you find yourself having to do stuff like this, it is often a sign of database design issues. Unfortunately, as programmers, sometimes we are "stuck" with what we are being provided, but if you have any control over it and have the ability to change it, you may want to look at doing so.

For example, based on what little I know about your problem here, I might hazard a guessing that instead of having 73 different tables you need to pull this data from, it sounds like maybe the data should all be in one (or just a few) tables instead. That would make things infinitely easier to work with. If you find yourself having multiple tables of similar design, it often times means that those tables can (and maybe should be) combined together.