Run query multiple times (different data parameter) to generate single dataset

[AOB]

Hi guys,

I have a reasonably complex query (3 subqueries into 1 main query) which gathers data from various tables into a single dataset based on a specified date.

I now need to generate a similar dataset but across a range of dates (a month) for reporting purposes. However, I can't just adapt the query and change the parameter from a "=#<Date>#" format to a "Between #<Date1># And #<Date2>#" format

The reason being, each date has to be treated individually and has to be queried as a standalone. It's to do with the type of data I have (one-to-many relationships between tables)

So what I really need to do is run the same query multiple times, for each date in scope, then stitch all of those datasets together into one 'giant' one.

But I have no idea how to do that in SQL (effectively, have one query produce the dates in scope, then join that onto the other query, passing each date as the parameter - I don't even think that's possible to be honest)

The other option I can think of is to use VBA to loop through the dates in scope, then use a QueryDef object to set the parameter and read the records for each date into a Recordset object. But then I have the problem of stitching all the Recordsets together, without looping through all the fields and rows each time.

I'm stumped - anybody have any suggestions? :banghead:

(Can post the SQL of the queries but it's quite large and didn't want to overload the thread - I'm just looking for a high-level approach!)

Thanks

Al

 

[CJ_London]

if you have to treat each date separately, can you use IN? i.e.

WHERE myDate IN (date1,date2, date3)

 

[Minty]

The VBA looping is probably the only way I'm afraid. If the result sets are the same you could append them into a temporary table rather than a record set.

I appreciate it wouldn't be 2 lines of code but I can't think of another way to achieve it.

 

[AOB]

Thanks guys

CJ, I don't believe I can use IN (for the same reason I can't use Between / And) - the parameter is in the subqueries so there's no way of passing the date across. And even if there was, I fear there would be an integrity issue with examining all the dates in parallel rather than in series.

Minty - I've used a loop in VBA and append each set of results to a local table. When all the dates are finished I then query the whole table into a recordset and pass that on to Excel (I only need it for reporting purposes). Then I just delete all the records in the table, ready for the next report.

It's not too bad - takes about 2-3 minutes to run through the 20 or so business days in a month. Only real drawback is that I can't create the local temp table at runtime (like I normally do) as the SELECT INTO / INSERT INTO returns an error ("You tried to assign the NULL value to a variable that is not a variant data type")

So I have to have the table already set up in the local FE with all the expected fields pre-defined. It sits empty until somebody tries to run the report (then gets filled, exported and cleaned out again when the report is finished)

Seems unnecessarily clunky but it works and isn't overly slow. You were right, it wasn't 2 lines of code either but managed to do it in about 40 (referring to the pre-defined queries and using the QueryDef object to adjust the parameters) It will be acceptable given that this particular report will only have to be run by one person, once a month. Anything more often than that and I probably would have to redesign some stuff.

Thanks for the advice!

Al

P.S. A separate question - which I hope doesn't require a new thread! - I'm not getting notifications any more for some reason? I only saw these responses by randomly popping in to check? I'm set up to receive them, and was receiving them previously, but no longer? Any ideas??