Optimize or simplify MS Access query

Question

I have Access query which really takes time to execute. Right now, I had to go through to at least four queries to get the result I want which is Top 4 percentage by group. Here are the queries.

Query1 - Add calculated field which is column3.
Query2 - Add cumulative field for column3.
Query3 - Add calculated fields in which I used column3 for calculation. calcfield1, calcfield2, calcfield3 combined them in one column (NewColumn) using UNION query. Sum the NewColumn and grouped them by GroupID.
Query4 - Selects the Top 4 values in NewColumn by GroupID.

Sample Output (Top 4 values of NewColumn by GroupID):

Group1 1.45
Group1 1.00
Group1 0.45
Group1 0.20
Group2 8.20
Group2 4.48
Group2 3.44
Group2 2.00

When I run Query4, it takes a while to execute (at least 10 mins.). The queries has to return all record because of the cumulative percentage calculation.

I tried HAVING clause in Query3 so that rows in Query4 would be minimized when I select the Top 4 but I couldn't get it worked. I also tried getting the Top 4 in Query2 so that succeeding query has less rows to work with but the query takes more time that I have to break the execution of query.

I'd like to simplify or at least optimize the query.

Table Structure:

 Table1:
 ID - Autonumber
 GroupID
 Tip1
 Tip2
 Tip3
 Tip4

Sample data Table1:
GroupID    Tip1      Tip2        Tip3      Tip4
171        1          5           4          8
172        2          7           5          3
173        8          16          10          

Table2:
ID - Autonumber
GroupID
Result1
Result2
Result3

Sample data Table2:
GroupID    Result1  Result2     Result3
171        1          5           4    
172        12         7           5    
173        8          6           3

Table1 and Table2 are joined using GroupID

can you show your table structure and some sample input data — rene
Yes, Query3 must have been the bottleneck because of the UNION of three fields into one. If I can get the Top 4 by group in Query3, I can eliminate Query4. But I don't know how to do that since I have to use HAVING clause with Select Top 4 subquery. — matley
Update: Only Query4 is very slow. Other queries return records in reasonable time. — matley

PowerUser PowerUser · Accepted Answer · 2013-05-30T18:47:49

I had a similar situation once where I had to rank data through a complicated self-join. It was very slow until I first dumped the data into a temp table and then ran update queries on it. Still slow, but not nearly as bad.

Perhaps you could use a staging table instead of a series of joined queries? Then #1,#2, and #3 could be update queries that act on that staging table.

Optimize or simplify MS Access query

1 Answers