Access SQL: Not a valid alisas name

[SQLScholar]

Hey all,

I am using a program external of access, to query an access DB via ODBC/SQL.

I am now having a strange problem. I have a long peice of code with lots of union queries and suchlike to make a formatted report.

I am getting to the point now where it seems that if i add another chunk of union query i get an error of "not a valid alias name".

Well i removed code untill i got it to work - then found that if i added any more, it seems to error. Even if i copy one of the other chunks a second time it errors and that runs fine.

Just put in the chunk that seems to make it error - works fine.

Any one got any ideas. I can post code if you wish, but its alot!

TIA

Dan

----------------------------------------
There are 2 types of computer, the prototype and the obsolete!!

 

[PHV]

Normally you use column's aliases only in the first select clause of an union query.

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[SQLScholar]

Sorry to be a pain - but i think i have solved it. I have gone over the limit for the amount of unions i am allowed (i believe its 20).

Any way around this?

Dan

----------------------------------------
There are 2 types of computer, the prototype and the obsolete!!

 

[JonFer]

Please post the SQL for the union query (several of the selects at least). You may be able to generate the records you need more easily.

 

[scriverb]

If JonFer's look at your SQL does not provide a more effecient way to create your recordset you can create multiple UNION queries and then UNION them together in one additional UNION taken both of them in as input. This can be more than just two but I just used two as an example.

Or, you can create a UNION and take it in recordsource to a make-table query and then append additional union onto that table structure. This has a little overhead with database bloat but it is another method.

[COLOR=006633]Bob Scriver[/color]

[COLOR=white 006633]MSU Spartan[/color]