Shorten union query

G

graveyard

Registered User.
Local time
Today, 06:03
Joined
Mar 26, 2011
Messages
55
Hi ppl, i have combined a few queries using an union but when i tried to run it, there is an error message "System resources exceeded"

When i researched online, it seems the union was badly designed and as a result, exceeded the character limit.

ive read some proposed solutions such as "not prefixing field names with [table name] when the
field name is not used in more than one table in the FROM
clause" and "excluding field names of other select stms" at http://us.generation-nt.com/answer/run-time-error-3035-system-resource-exceeded-help-201384272.html

however, i dont quite get the solutions and ended up having error when i tried some trial and error methods. Can anyone advise on how to optimise an union query so its not too lengthy and so it will run properly.

Below is part of the SQL of the union - as you can see, too much details.


SELECT "Scenario A" AS Expr1, [Scenario A].Source, [Scenario A].Label, [Scenario A].Period, [Scenario A].Category, [Scenario A].Origin_Region, [Scenario A].AP_Country, [Scenario A].Month, [Scenario A].Month1, [Scenario A].Quarter, [Scenario A].YTD, [Scenario A].Sector, [Scenario A].Bleed, [Scenario A].[Route Impact], [Scenario A].Org, [Scenario A].Dest, [Scenario A].Dest_Region, [Scenario A].Currency, [Scenario A].Weight, [Scenario A].[Freight (LCCY)], [Scenario A].[FSC (LCCY))], [Scenario A].[FSC Adj (LCCY)], [Scenario A].[Handling (LCCY)], [Scenario A].[Ground Handling (LCCY)], [Scenario A].[Security (LCCY)], [Scenario A].[Ticket Costs (LCCY)], [Scenario A].[Tax (LCCY)], [Scenario A].[Others (LCCY)], [Scenario A].[Total CAL Costs (LCCY)], [Scenario A].[Freight (LCCY) Cpk], [Scenario A].[FSC (LCCY)) Cpk], [Scenario A].[FSC Adj (LCCY) Cpk], [Scenario A].[Handling (LCCY) Cpk], [Scenario A].[Ground Handling (LCCY) Cpk], [Scenario A].[Security (LCCY) Cpk], [Scenario A].[Ticket Costs (LCCY) Cpk], [Scenario A].[Tax (LCCY) Cpk], [Scenario A].[Others (LCCY) Cpk], [Scenario A].[2011AOPR], [Scenario A].[Freight @11 AOPR], [Scenario A].[FSC @11 AOPR], [Scenario A].[FSC Adj@11 AOPR], [Scenario A].[Handling @ 11 AOPR], [Scenario A].[Ground Handling @11 AOPR], [Scenario A].[Security @11 AOPR], [Scenario A].[Ticket Costs @11 AOPR], [Scenario A].[Tax @11 AOPR], [Scenario A].[Others @11 AOPR], [Scenario A].[Total CAL Costs @11 AOPR], [Scenario A].[Freight @11 AOPR Cpk], [Scenario A].[FSC @11 AOPR Cpk], [Scenario A].[FSC Adj@11 AOPR Cpk], [Scenario A].[Handling @ 11 AOPR Cpk], [Scenario A].[Ground Handling @11 AOPR Cpk], [Scenario A].[Security @11 AOPR Cpk], [Scenario A].[Ticket Costs @11 AOPR Cpk], [Scenario A].[Tax @11 AOPR Cpk], [Scenario A].[Others @11 AOPR Cpk], [Scenario A].[2012AOPR], [Scenario A].[Freight @12AOPR], [Scenario A].[FSC @12AOPR], [Scenario A].[FSC Adj @12AOPR], [Scenario A].[Handling @12AOPR], [Scenario A].[Ground Handling @12AOPR], [Scenario A].[Security @12AOPR], [Scenario A].[Ticket Costs @12AOPR], [Scenario A].[Tax @12AOPR], [Scenario A].[Others @12AOPR], [Scenario A].[Total CAL Costs @12AOPR], [Scenario A].[Freight @12AOPR Cpk], [Scenario A].[FSC @12AOPR Cpk], [Scenario A].[FSC Adj @12AOPR Cpk], [Scenario A].[Handling @12AOPR Cpk], [Scenario A].[Ground Handling @12AOPR Cpk], [Scenario A].[Security @12AOPR Cpk], [Scenario A].[Ticket Costs @12AOPR Cpk], [Scenario A].[Tax @12AOPR Cpk], [Scenario A].[Others @12AOPR Cpk], [Scenario A].[Mvt related to], [Scenario A].[ORIGINAL SECTOR], [Scenario A].LOOKUP, [Scenario A].FCT, [Scenario A].[ORG-], [Scenario A].[DEST-], [Scenario A].TYPE
FROM [Scenario A]
GROUP BY [Scenario A].Source, [Scenario A].Label, [Scenario A].Period, [Scenario A].Category, [Scenario A].Origin_Region, [Scenario A].AP_Country, [Scenario A].Month, [Scenario A].Month1, [Scenario A].Quarter, [Scenario A].YTD, [Scenario A].Sector, [Scenario A].Bleed, [Scenario A].[Route Impact], [Scenario A].Org, [Scenario A].Dest, [Scenario A].Dest_Region, [Scenario A].Currency, [Scenario A].Weight, [Scenario A].[Freight (LCCY)], [Scenario A].[FSC (LCCY))], [Scenario A].[FSC Adj (LCCY)], [Scenario A].[Handling (LCCY)], [Scenario A].[Ground Handling (LCCY)], [Scenario A].[Security (LCCY)], [Scenario A].[Ticket Costs (LCCY)], [Scenario A].[Tax (LCCY)], [Scenario A].[Others (LCCY)], [Scenario A].[Total CAL Costs (LCCY)], [Scenario A].[Freight (LCCY) Cpk], [Scenario A].[FSC (LCCY)) Cpk], [Scenario A].[FSC Adj (LCCY) Cpk], [Scenario A].[Handling (LCCY) Cpk], [Scenario A].[Ground Handling (LCCY) Cpk], [Scenario A].[Security (LCCY) Cpk], [Scenario A].[Ticket Costs (LCCY) Cpk], [Scenario A].[Tax (LCCY) Cpk], [Scenario A].[Others (LCCY) Cpk], [Scenario A].[2011AOPR], [Scenario A].[Freight @11 AOPR], [Scenario A].[FSC @11 AOPR], [Scenario A].[FSC Adj@11 AOPR], [Scenario A].[Handling @ 11 AOPR], [Scenario A].[Ground Handling @11 AOPR], [Scenario A].[Security @11 AOPR], [Scenario A].[Ticket Costs @11 AOPR], [Scenario A].[Tax @11 AOPR], [Scenario A].[Others @11 AOPR], [Scenario A].[Total CAL Costs @11 AOPR], [Scenario A].[Freight @11 AOPR Cpk], [Scenario A].[FSC @11 AOPR Cpk], [Scenario A].[FSC Adj@11 AOPR Cpk], [Scenario A].[Handling @ 11 AOPR Cpk], [Scenario A].[Ground Handling @11 AOPR Cpk], [Scenario A].[Security @11 AOPR Cpk], [Scenario A].[Ticket Costs @11 AOPR Cpk], [Scenario A].[Tax @11 AOPR Cpk], [Scenario A].[Others @11 AOPR Cpk], [Scenario A].[2012AOPR], [Scenario A].[Freight @12AOPR], [Scenario A].[FSC @12AOPR], [Scenario A].[FSC Adj @12AOPR], [Scenario A].[Handling @12AOPR], [Scenario A].[Ground Handling @12AOPR], [Scenario A].[Security @12AOPR], [Scenario A].[Ticket Costs @12AOPR], [Scenario A].[Tax @12AOPR], [Scenario A].[Others @12AOPR], [Scenario A].[Total CAL Costs @12AOPR], [Scenario A].[Freight @12AOPR Cpk], [Scenario A].[FSC @12AOPR Cpk], [Scenario A].[FSC Adj @12AOPR Cpk], [Scenario A].[Handling @12AOPR Cpk], [Scenario A].[Ground Handling @12AOPR Cpk], [Scenario A].[Security @12AOPR Cpk], [Scenario A].[Ticket Costs @12AOPR Cpk], [Scenario A].[Tax @12AOPR Cpk], [Scenario A].[Others @12AOPR Cpk], [Scenario A].[Mvt related to], [Scenario A].[ORIGINAL SECTOR], [Scenario A].LOOKUP, [Scenario A].FCT, [Scenario A].[ORG-], [Scenario A].[DEST-], [Scenario A].TYPE
UNION SELECT "Scenario BB" AS Expr1, [Scenario BB].Source, [Scenario BB].Label, [Scenario BB].Period, [Scenario BB].Category, [Scenario BB].Origin_Region, [Scenario BB].AP_Country, [Scenario BB].Month, [Scenario BB].Month1, [Scenario BB].Quarter, [Scenario BB].YTD, [Scenario BB].Sector, [Scenario BB].Bleed, [Scenario BB].[Route Impact], [Scenario BB].Org, [Scenario BB].Dest, [Scenario BB].Dest_Region, [Scenario BB].Currency, [Scenario BB].Weight, [Scenario BB].[Freight (LCCY)], [Scenario BB].[FSC (LCCY))], [Scenario BB].[FSC Adj (LCCY)], [Scenario BB].[Handling (LCCY)], [Scenario BB].[Ground Handling (LCCY)], [Scenario BB].[Security (LCCY)], [Scenario BB].[Ticket Costs (LCCY)], [Scenario BB].[Tax (LCCY)], [Scenario BB].[Others (LCCY)], [Scenario BB].[Total CAL Costs (LCCY)], [Scenario BB].[Freight (LCCY) Cpk], [Scenario BB].[FSC (LCCY)) Cpk], [Scenario BB].[FSC Adj (LCCY) Cpk], [Scenario BB].[Handling (LCCY) Cpk], [Scenario BB].[Ground Handling (LCCY) Cpk], [Scenario BB].[Security (LCCY) Cpk], [Scenario BB].[Ticket Costs (LCCY) Cpk], [Scenario BB].[Tax (LCCY) Cpk], [Scenario BB].[Others (LCCY) Cpk], [Scenario BB].[2011AOPR], [Scenario BB].[Freight @11 AOPR], [Scenario BB].[FSC @11 AOPR], [Scenario BB].[FSC Adj@11 AOPR], [Scenario BB].[Handling @ 11 AOPR], [Scenario BB].[Ground Handling @11 AOPR], [Scenario BB].[Security @11 AOPR], [Scenario BB].[Ticket Costs @11 AOPR], [Scenario BB].[Tax @11 AOPR], [Scenario BB].[Others @11 AOPR], [Scenario BB].[Total CAL Costs @11 AOPR], [Scenario BB].[Freight @11 AOPR Cpk], [Scenario BB].[FSC @11 AOPR Cpk], [Scenario BB].[FSC Adj@11 AOPR Cpk], [Scenario BB].[Handling @ 11 AOPR Cpk], [Scenario BB].[Ground Handling @11 AOPR Cpk], [Scenario BB].[Security @11 AOPR Cpk], [Scenario BB].[Ticket Costs @11 AOPR Cpk], [Scenario BB].[Tax @11 AOPR Cpk], [Scenario BB].[Others @11 AOPR Cpk], [Scenario BB].[2012AOPR], [Scenario BB].[Freight @12AOPR], [Scenario BB].[FSC @12AOPR], [Scenario BB].[FSC Adj @12AOPR], [Scenario BB].[Handling @12AOPR], [Scenario BB].[Ground Handling @12AOPR], [Scenario BB].[Security @12AOPR], [Scenario BB].[Ticket Costs @12AOPR], [Scenario BB].[Tax @12AOPR], [Scenario BB].[Others @12AOPR], [Scenario BB].[Total CAL Costs @12AOPR], [Scenario BB].[Freight @12AOPR Cpk], [Scenario BB].[FSC @12AOPR Cpk], [Scenario BB].[FSC Adj @12AOPR Cpk], [Scenario BB].[Handling @12AOPR Cpk], [Scenario BB].[Ground Handling @12AOPR Cpk], [Scenario BB].[Security @12AOPR Cpk], [Scenario BB].[Ticket Costs @12AOPR Cpk], [Scenario BB].[Tax @12AOPR Cpk], [Scenario BB].[Others @12AOPR Cpk], [Scenario BB].[Mvt related to], [Scenario BB].[ORIGINAL SECTOR], [Scenario BB].LOOKUP, [Scenario BB].FCT, [Scenario BB].[ORG-], [Scenario BB].[DEST-], [Scenario BB].TYPE
FROM [Scenario BB]
GROUP BY [Scenario BB].Source, [Scenario BB].Label, [Scenario BB].Period, [Scenario BB].Category, [Scenario BB].Origin_Region, [Scenario BB].AP_Country, [Scenario BB].Month, [Scenario BB].Month1, [Scenario BB].Quarter, [Scenario BB].YTD, [Scenario BB].Sector, [Scenario BB].Bleed, [Scenario BB].[Route Impact], [Scenario BB].Org, [Scenario BB].Dest, [Scenario BB].Dest_Region, [Scenario BB].Currency, [Scenario BB].Weight, [Scenario BB].[Freight (LCCY)], [Scenario BB].[FSC (LCCY))], [Scenario BB].[FSC Adj (LCCY)], [Scenario BB].[Handling (LCCY)], [Scenario BB].[Ground Handling (LCCY)], [Scenario BB].[Security (LCCY)], [Scenario BB].[Ticket Costs (LCCY)], [Scenario BB].[Tax (LCCY)], [Scenario BB].[Others (LCCY)], [Scenario BB].[Total CAL Costs (LCCY)], [Scenario BB].[Freight (LCCY) Cpk], [Scenario BB].[FSC (LCCY)) Cpk], [Scenario BB].[FSC Adj (LCCY) Cpk], [Scenario BB].[Handling (LCCY) Cpk], [Scenario BB].[Ground Handling (LCCY) Cpk], [Scenario BB].[Security (LCCY) Cpk], [Scenario BB].[Ticket Costs (LCCY) Cpk], [Scenario BB].[Tax (LCCY) Cpk], [Scenario BB].[Others (LCCY) Cpk], [Scenario BB].[2011AOPR], [Scenario BB].[Freight @11 AOPR], [Scenario BB].[FSC @11 AOPR], [Scenario BB].[FSC Adj@11 AOPR], [Scenario BB].[Handling @ 11 AOPR], [Scenario BB].[Ground Handling @11 AOPR], [Scenario BB].[Security @11 AOPR], [Scenario BB].[Ticket Costs @11 AOPR], [Scenario BB].[Tax @11 AOPR], [Scenario BB].[Others @11 AOPR], [Scenario BB].[Total CAL Costs @11 AOPR], [Scenario BB].[Freight @11 AOPR Cpk], [Scenario BB].[FSC @11 AOPR Cpk], [Scenario BB].[FSC Adj@11 AOPR Cpk], [Scenario BB].[Handling @ 11 AOPR Cpk], [Scenario BB].[Ground Handling @11 AOPR Cpk], [Scenario BB].[Security @11 AOPR Cpk], [Scenario BB].[Ticket Costs @11 AOPR Cpk], [Scenario BB].[Tax @11 AOPR Cpk], [Scenario BB].[Others @11 AOPR Cpk], [Scenario BB].[2012AOPR], [Scenario BB].[Freight @12AOPR], [Scenario BB].[FSC @12AOPR], [Scenario BB].[FSC Adj @12AOPR], [Scenario BB].[Handling @12AOPR], [Scenario BB].[Ground Handling @12AOPR], [Scenario BB].[Security @12AOPR], [Scenario BB].[Ticket Costs @12AOPR], [Scenario BB].[Tax @12AOPR], [Scenario BB].[Others @12AOPR], [Scenario BB].[Total CAL Costs @12AOPR], [Scenario BB].[Freight @12AOPR Cpk], [Scenario BB].[FSC @12AOPR Cpk], [Scenario BB].[FSC Adj @12AOPR Cpk], [Scenario BB].[Handling @12AOPR Cpk], [Scenario BB].[Ground Handling @12AOPR Cpk], [Scenario BB].[Security @12AOPR Cpk], [Scenario BB].[Ticket Costs @12AOPR Cpk], [Scenario BB].[Tax @12AOPR Cpk], [Scenario BB].[Others @12AOPR Cpk], [Scenario BB].[Mvt related to], [Scenario BB].[ORIGINAL SECTOR], [Scenario BB].LOOKUP, [Scenario BB].FCT, [Scenario BB].[ORG-], [Scenario BB].[DEST-], [Scenario BB].TYPE
HAVING ((([Scenario BB].Source)="2012AOP"))
UNION ....................
 
I don't think anyone will be able to give you any productive advice by looking at your huge sql statement. Unless they have lots of time to spare ;)

The best thing to do is to break up your Union queries and add them one-by-one, test and run it until you find the one that gives you a problem.
 
sorry, just to add. - i was trying to combine the different queries with just one different field name "Scenario A", "Scenario BB" ... and the rest of fieldnames "Source","Label","Period" etc.. are same
 
Is the difference between the queries in the HAVING clause?
 
vbaInet said:
I don't think anyone will be able to give you any productive advice by looking at your huge sql statement. Unless they have lots of time to spare ;)

The best thing to do is to break up your Union queries and add them one-by-one, test and run it until you find the one that gives you a problem.
Click to expand...


hi vbaInet, you are right. the sql ive pasted is awful! Sure, will try to do that though i really think its the length of the sql thats preventing me from running the union. what i did is open the query in sql view and copy paste into union, was thinking if theres a better way of doing as i get a huge chunk of SQL for each query i opened ..
 
Perhaps you can tell us exactly what you're trying to do with your queries?

Give us some sample records in a spreadsheet and show us the raw data + your expected results. We might be able to come up with a much better way of doing this.

Or upload a stripped down version of your db.
 
thanks for your time and sorry to bother. i hv atatched a spreadsheet with the details. pls let me know if you need more info / details .. hope this is not confusing
 

Attachments

Very nicely presented. That's what we like to see.

So, what is the criteria for each of the individual queries? Is it just filtering on the Expr1 field?
 
I have a suggestion that may or may not work for you. The error message that you are getting is primarily due to the fact that a query can contain only so many characters. The easiest way to reduce the number of characters would be to create aliases for your table names. For instance:

If You change the characters [Scenario A] to the Alias SA, you get a savings of 9 characters per usage, and a grand total of over 1500 characters in the [Scenario A] Query. While this may seem like a lot of savings, it will only work if the number of joined UNIONS is small. A better way might be to have each of the Queries that are to be combined (Modified or unmodified) as an individual query that creates a table with the same Column Names in the same order and then have the Union query look something like this:

SELECT Item1, Item2, Item3, ... ItemLast
FROM ScenarioAQuery
UNION SELECT Item1, Item2, Item3, ... ItemLast
FROM ScenarioBBQuery
.
.
.
UNION SELECT Item1, Item2, Item3, ... ItemLast
FROM ScenarioLastQuery

(Optional GROUP BY and ORDER BY Queries go here)
 
vbaInet said:
Very nicely presented. That's what we like to see.

So, what is the criteria for each of the individual queries? Is it just filtering on the Expr1 field?
Click to expand...


thanks. its much clearer i have to admit. yes all these queries are created out of linked files and i need to identify the data specific to each linked file so i created the expression column named "Scenario A", "Scenario B" ...etc before attempting to combine these together.

the key idea is just to combine all these into a master dataset
 
Did you see MSAccessRookie's advice? If you use a shorter Alias you will save lots of characters.

In any case, what you can do is to append your data to the master table without needing to union. Just insert them one table after the other.
 
vbaInet said:
Did you see MSAccessRookie's advice? If you use a shorter Alias you will save lots of characters.

In any case, what you can do is to append your data to the master table without needing to union. Just insert them one table after the other.
Click to expand...

yes, i saw the advice from MsAccessRookie. I will try the shorter alias method that was advised

yes i thought of appending the queries. but the issue is there will be sunsequent updates to the data (thats why i used linked files to excel spreadsheets in whcih the data will be uppdated) and i believe if i used union query, the data will always be updated based on the linked files i have so there is no need for me to keep on appending the data everytime the file is updated
 
There's no updating in Union queries. Once you add UNION it becomes a read-only and static recordset.
 
MSAccessRookie said:
I have a suggestion that may or may not work for you. The error message that you are getting is primarily due to the fact that a query can contain only so many characters. The easiest way to reduce the number of characters would be to create aliases for your table names. For instance:

If You change the characters [Scenario A] to the Alias SA, you get a savings of 9 characters per usage, and a grand total of over 1500 characters in the [Scenario A] Query. While this may seem like a lot of savings, it will only work if the number of joined UNIONS is small. A better way might be to have each of the Queries that are to be combined (Modified or unmodified) as an individual query that creates a table with the same Column Names in the same order and then have the Union query look something like this:

SELECT Item1, Item2, Item3, ... ItemLast
FROM ScenarioAQuery
UNION SELECT Item1, Item2, Item3, ... ItemLast
FROM ScenarioBBQuery
.
.
.
UNION SELECT Item1, Item2, Item3, ... ItemLast
FROM ScenarioLastQuery

(Optional GROUP BY and ORDER BY Queries go here)
Click to expand...


hi MSAccessRookie, thank you for your advice. i will change the alias to a shorter form as you suggested (SA for Scenario A, SB for Scenario B..etc) and hope it will work out

On your other suggestion, do you mean i have to just type in the fieldname without copying/pasting the entire SQL .. so it will be like :

SELECT "Label", "Source", "Country", ... ItemLast
FROM ScenarioAQuery
UNION SELECT "Label", "Source", "Country", ... ItemLast
FROM ScenarioBBQuery

Or do you mean i have to use make table query?
 
vbaInet said:
There's no updating in Union queries. Once you add UNION it becomes a read-only and static recordset.
Click to expand...

oh my! i actually thought it works like join queries which auto update

you mean assuming i do not add any additional column and if i change the value under a fieldname ..say, from 1,000 to 2,000, in the linked tables(whcih the select queries are created on) and i double click on the union query, it will still show 1,000???
 
graveyard said:
you mean assuming i do not add any additional column and if i change the value under a fieldname ..say, from 1,000 to 2,000, in the linked tables(whcih the select queries are created on) and i double click on the union query, it will still show 1,000???
Click to expand...
Only if you manually refresh the query.
 
vbaInet said:
Only if you manually refresh the query.
Click to expand...

dats good enough. i just have to push a button and have the latest data. if i use the append method, i will have to keep appending every time there is a change and i may get lost with so much data involved. so i hope to be able to use the union

i am going to shorten the alias and also try the union setup mentioned by MsAccess Rookie (btw do you understand what he said abt the setup, i didnt so i asked him again - sorry i am the real noob here so pls be patient if my question sounds stupid to u guys here) and hopefully can resolve
 
graveyard said:
hi MSAccessRookie, thank you for your advice. i will change the alias to a shorter form as you suggested (SA for Scenario A, SB for Scenario B..etc) and hope it will work out

On your other suggestion, do you mean i have to just type in the fieldname without copying/pasting the entire SQL .. so it will be like :

SELECT "Label", "Source", "Country", ... ItemLast
FROM ScenarioAQuery
UNION SELECT "Label", "Source", "Country", ... ItemLast
FROM ScenarioBBQuery

Or do you mean i have to use make table query?
Click to expand...

Don't forget the idea of making 'n' separate queries for each UNION member and JOINing them together the way that I suggested.

**** I need to get my glassses cleaned. ****

Your example is effectively what I was suggesting. It turns out that as long as the Queries have the same FieldNames in the same order, you can shorten that to SELECT *
 
Last edited:
MSAccessRookie said:
Your example is effectively what I was suggesting. It turns out that as long as the Queries have the same FieldNames in the same order, you can shorten that to SELECT *
Click to expand...
Good point MSAccessRookie.

@graveyard: Remember to arrange your fields in the right order if you were to use the above suggestion from MSAccessRookie.
 
vbaInet said:
Good point MSAccessRookie.

@graveyard: Remember to arrange your fields in the right order if you were to use the above suggestion from MSAccessRookie.
Click to expand...

hi vbainet, i tried MsAccessRookie's query method but got the same error - system resource exceeded. i didnt change the alias name but i m gg to do it now. do you know if this will make any difference in terms of getting the query streamlined and produce results .. now that i am using "select*" in the union instead of follwoing

SELECT "Label", "Source", "Country", ... ItemLast
FROM ScenarioAQuery
UNION SELECT "Label", "Source", "Country", ... ItemLast
FROM ScenarioBBQuery


i am at the same time changing the name but its taking way too long time ,.,, think database is too heavy ... so just thought of checking while i am doing this

 
You must log in or register to reply here.

Similar threads

I
can anyone help me with normalisation im really really stuck!
Replies
1
Views
1,664
Pat Hartman
P

Users who are viewing this thread

Total: 1 (members: 0, guests: 1)
Back
Top Bottom