Hmmm. Well, tightening the VB is a good start anyway. Not knowing your data and your application, it's hard to be any more help. I suspect that breaking down the requirements into discrete chunks might help, and temp tables in the frontend may ease things somewhat, but then you're trading off data flow over a network with burden on the back end.

I assume that, since you can create one query, you should be able to create three or five queries. Therefore, my strategy would be to decompose the gross requirement to create multiple discrete source queries at the backend and then build up the final result query from the source queries. (That's my initial default strategy for any complex query, anyway.)

But, you know your data and your application, and you have to support the thing, so best wishes. Please let us know if we can help in any way again.