SQL experts please help with a monster query.

Short answer: you can’t.

Here’s the deal. A VB recordset is an object that is defined by VB. It bears no relation to the SQL server – in fact, it’s designed to be abstract from the SQL server. This is why you can use a VB Recordset object with MS SQL, Oracle, mySql, Excel, and so on. Basically, anything that can use the ODBC standard can fit into a Recordset object.

The Recordset object itself may or may not be writeable, depending on the connection type, permissions, and a host of other things, but one thing it is never capable of doing is being used in SQL statements on the server. It doesn’t exist as far as the server is concerned – it’s strictly local to the machine that’s sending the requests to the server.

So, the only way to ‘use’ a VB recordset in subsequent queries is to step through the recordset in some fashion and construct SQL statements out of the data it contains. This is a useful technique, sometimes, but most often its MUCH better, faster, and more efficient to simply construct the SQL statement in such a way as to get you the results you need in the first place, rather than mashing them about after the fact.

I don’t want to suggest this, but as a possible example, you could construct a query that returns a recordset containing the summary information for each account, then use a key field (contained in that recordset) to construct a SQL statement that only returns the data you need for each applicable row in the recordset. Honestly, I think you’re better off using stored procedures to do this if that’s what you need, but it may be possible.

What I’ve ended up doing is defining a stored procedure which builds a table with all records that satisfy the condition (but no agregate functions or anything… just the pure raw data)

I then defined a DTS which runs it in the morning.

And then my asp uses that table using the query, without the distinct keyword… and with an extra line for earliest time as well as earliest date…
the results are instantaneous and not duplicated - perfect.

Many thanks to all the people who replied here and gave me ideas and solutions to achieve the final end.