VBA Union Query

[Littlemalky]

I have a union query with 26 subsequent queries built in Access; however, I get an error saying "System resource exceeded.". I'm assuming I just have too many queries for it to process. If it cut out four of them it works fine. So my question is, can I somehow do a union in VBA with the ability to run 26 queries that would be more efficient so I wouldn't run out of resources? If so, is there a way to just reference the name of the queries instead of pasting the SQL code? Not sure how to structure this.

 

[Joe4]

Let's talk about your data structure a bit.

Are you saying that you have a Union Query that is made up of 26 inidividual queries? If so, why do many? What do you data tables look like?

Or are you saying that you have 26 individual queries that use the single Union Query as their Data Source? If so, what does this Union Query look like? Can you post the code of it here?

 

[Littlemalky]

So, yes, I created 26 queries that have distinct logic but the same fields. I tried to copy the SQL in each of them to create a big union, but it maxes out at 24 unfortunately. I'm looking for an alternative route because I want to use this union query to append to a table where I store the data. I'm trying to avoid splitting it into two separate union queries. Just curious if there is an easy way to do this in VB that could support 26 of them.

 

[Joe4]

I created 26 queries that have distinct logic but the same fields.

Does this need to be 26 distinct queries? (if so, why)?
Can some of these queries be combined by using operands like AND and OR in your criteria?

I want to use this union query to append to a table where I store the data.

Have you considered changing the 26 queries to append queries (as opposed to using a Union query)?
Note if the issue is the amount of time it would take to run 26 append queries, you can create a query to run/append all those queries with a single click.

 

[Littlemalky]

Believe me, I wish I could. These are for calculating sales commissions and they all have very specific and unique compensation plans so there is no logic I can build that crosses over. Trust me, I've tried.

Yeah I suppose that's not a bad idea with the append queries. The union doesn't take all that long to run. It just simplified my append query so I could just have one, but I guess at the end of the day it doesn't matter.

 

[Joe4]

Believe me, I wish I could. These are for calculating sales commissions and they all have very specific and unique compensation plans so there is no logic I can build that crosses over. Trust me, I've tried.

I am curious then, how do these queries decide what to include?
Are they all running off of the same data table?

Everything you have provided regarding the details has been very general, so it is very difficult to give you specific advice. But I am pretty certain that there is a better way. You have been able to differentiate these queries so far somehow.

One possibility may be to create a User Defined Function.

 

[Littlemalky]

So, there are multiple tables I'm using to get to the end query, but all of these queries are layered with subqueries to use different tables and calculations and criteria. The end result is the same format with the payout date, employee id, position id, product id, active sales, active quota, active baseline, etc.

 

[Joe4]

Interesting. I am guessing that you may be working with a table structure that isn't quite normalized, which can make things a lot harder than they have to be.

 

[Fazza]

This reminded me of an old Excel thread where tightwad wanted to UNION many tables. My old post #56 has some explanation
https://www.mrexcel.com/forum/excel-questions/315768-creating-pivot-table-multiple-sheets-6.html

A limit was reached at 50 union queries and it was found that a workaround was make a union query from up to 49 tables, and that 2 of these such queries could be used. Or 3 queries each of 48, or 4 queries each of 47, etc

Presumably something similar can be done in MS Access. Such as try making a UNION of two sub-queries each a UNION of 13 tables.

 

[Littlemalky]

My Tables are absolutely normalized. There is blanket logic I can apply to all of the roles and products. Each has very specific criteria that I cannot apply to everything at once. There isn't consistency between all of the compensation plans. They all have very unique structures. So i have to filter them with unique criteria accordingly. The end result is the same columns, but there are many roads to get there. It's hard to explain the dynamics of it on this log.

I was able to convert everything to append queries. That was a great solution and worked well for me. I appreciate the help!