Union query (1 Viewer)

[Freddie b]

I have a access union query on multiple queries (about 25), but it runs very slow even with 2-3 rows/each query. What can do in this case

 

[ebs17]

To execute the union query, all subqueries used must be executed. So you would have to look at each of these sub-queries, what it does, what exact SQL statement it has, whether it is optimally designed and uses indexes, for example, and which original data sets are evaluated.
The union query is the sum of the partial efforts plus your own effort.

What can do

In about 70 percent of cases, union queries are necessary because there is incorrect table modeling.
Compiling from 25 parts is not a normal case, the same data should actually be present in the same fields of the same table according to normalization rules.

So the first action would be to check the database schema and the actual process.

 

[Isaac]

I have a access union query on multiple queries (about 25), but it runs very slow even with 2-3 rows/each query. What can do in this case

This is a massive generalization, and just my own personal opinion, but if I had to just take a wild guess at what aspect of things were causing people to perceive that their Union queries, especially, were slower than expected .... I'd guess it is the Distincting element, which can be very expensive and may well not even be needed nor intended in some use cases.

 

[tvanstiphout]

This is a massive generalization, and just my own personal opinion, but if I had to just take a wild guess at what aspect of things were causing people to perceive that their Union queries, especially, were slower than expected .... I'd guess it is the Distincting element, which can be very expensive and may well not even be needed nor intended in some use cases.

If not needed, then use UNION ALL.

 

[mike60smart]

I have a access union query on multiple queries (about 25), but it runs very slow even with 2-3 rows/each query. What can do in this case

I would suggest that you table structures are wrong.

Can you upload a screenshot of your Relationship Window?

 

[Freddie b]

I would suggest that you table structures are wrong.

Can you upload a screenshot of your Relationship Window?

This thing that I am trying to do is a database for accounting of purchases and sales. it is a table with approximately 700 account names. I have 5 forms with subforms (eg payment, payment detail) etc. One form for purchase, one for sale, for payment and 1 for cashing. the amount of the transaction in each of these forms is related to 4 to 5 accounts from the table of accounts, some go to debit some to credit. in the end the result of all these gives profit or loss. so the relationship scheme is very complicated and confused.

 

[mike60smart]

This is why we need to see your Relationship Window Screenshot.

 

[ebs17]

it is a table with approximately 700 account names

1 table isn't a complicated relationship scheme.

 

[GPGeorge]

This thing that I am trying to do is a database for accounting of purchases and sales. it is a table with approximately 700 account names. I have 5 forms with subforms (eg payment, payment detail) etc. One form for purchase, one for sale, for payment and 1 for cashing. the amount of the transaction in each of these forms is related to 4 to 5 accounts from the table of accounts, some go to debit some to credit. in the end the result of all these gives profit or loss. so the relationship scheme is very complicated and confused.

My guess, without being able to see the actual table ,is that this is a "spreadsheet" style table with multiple columns containing the same type of data (purchases, sales, etc.) instead of a properly normalized set of tables.

One of the unfortunate consequences of such non-normalized tables is the need to resort to union queries with a massive number of SELECT statements.

But again, that's a guess until we have more detail.

 

[CJ_London]

I've written a number of accounting applications over the years. All of them have a single transactions table which, depending on requirements, has either a single value column with a method or perhaps another field to identify whether it is a debit or credit or it has separate debit and credit fields. Both structures link back to customer/supplier, nominal codes etc.

The only time I might need to use a union query is if my transaction table structure is something like

dr account
cr account
value
(plus other fields of course)

where I need a list of all transactions for something like a detailed nominal ledger report - but it only needs two selects, the first to collect the dr accounts, the second the cr.

The alternative (and my preference as it retains the use of indexing) is to use a counter table in a cartesian relationship with the transactions table (i.e. no join) counter table is limited to 2 records (e.g. 0 and 1) and calculated values for fields that require it e.g. iif(countvalue,dr account, cr account), iif(countvalue, value, value*-1).

Similar principle can be used to create opening and closing balances within the same query (requires 3 or 4 records from the counter table and changed to an aggregate query)

The only other time I can envisage needing a union query is where a single report needs to be collated to provide a 'cascading view' - At the top - a short highlight , followed by summary by categories, then sub categories then detail. etc. However I see this as a 'quick and dirty' to get the job done in a query to export to excel. Could be done using a report with sub reports for example. Or longer term, include some 'reporting' fields in the nominal codes table with calculated values as indicated above.