Access Query, Based on queries, based on queries

**Xcelguy** · 11-12-2013, 03:04 PM

I'm nearly done with the backend dashboard I am working on developing. The problem is I am running into a problem with the last (bottom) level of the the dashboard.

Gen 5 Database Structure.jpg

Basically the dashboard looks at a bunch of raw data (close to 4 million rows) and uses other tables to do two things:
1) convert a nonplottable financial period into a plottable calendar period.
2) To aggregate at the smallest and next to smallest grain (top level green).

The results of these two grains are then combined for each window in order to create indices and measure comparative performance (2nd level green).

I am now working on combining the results of the 2nd level of green blocks in order to create comparisons between time periods in order to look for products that are trending in the wrong direction. However when I try to combine the results of the three queries (2nd level) that comprise this one query (3rd level) Access has a problem. Specifically "The query cannot be completed. Either the size of the query result is larger than the maximum size of a database (2GB), or there is not enough temporary storage space on the disk to store the query result".

Since I have 104 GB of free memory the latter option is probably not the case. This would mean that Access is saying there is not enough storage space inside the database to store the result. My understand was that queries do not substantially increase the size of the database as I was able to cut hundreds of MB by removing tables and keeping the format in a query.

So my question is this: "Does access need to have enough memory to store the datasets from all the queries that feed the ultimate 3rd level query"? My database is sitting at 1.5 GB at the moment. When the 2nd level queries were tables instead it was sitting at around 1.85 GB.

What is the ideal solution to this problem? Is there one?

Please advise,

**alansidman** · 11-12-2013, 04:34 PM

As an aside, during the building of a db, it is important to run a compact and repair on a regular basis. Do this and see if it helps to alleviate some of the file size limitation.

**Xcelguy** · 11-13-2013, 10:15 AM

I have and it doesnt

Any other suggestions? I am thinking i might just pass through the three queries that comprise this query into tables in another file so it doesnt have to do the precalc work.