Three year analysis - Quey per year

[SamDeMan]

Hi

I was wondering if anybody has some ideas. i need to create a report that has three years of history. it is hard to describe the entire project, but i will describe it breifly.

the user enters a date range (usually would be jan 1 until some date within the same year). i then want to show the data for that date range in the first column, and then the past two years in the next two columns. the problem is that the underlying query is different for each of those columns. to be a bit more specific, for year 0 i need to split the data based on the date range and in year -1 and year -2 i need to have a different query per year.

i would love to write all this in VBA, however, a report needs to have be bounded with one and only one recordset. i know that in DAO i was able to create at runtime a querydef that would maybe help. however i use only ADO. this last part i am not sure makes a difference. basically what would be optimal for me would be to some how attach a recordset to each column. anybody saw anything resembling this, i would be happy if you share it.

thanks,

sam

 

[SamDeMan]

Thanks for your reply. It definetly opened my eyes to something i haven't thaught about. However, this is my situation.

each date range must split up date with an if statement. basically my data is not based by day it is by payperiod, which means i need to split up a span of two weeks and take out the fractional time elapsed. so lets say the parameters are 1/1/xx - 12/31/xx i need to take all which happened 12/19/xx-1 until 1/13/xx+1 and then have a multiplier which multiplies some columns. this query runs for more than a minute.

therefore i am trying to get three queries going in VBA so that it is easier to set up. otherwise it may take me a long time to set up and then each time its run. i don't think that if it takes five minutes it would bother me, i just hope i wouldn't have a problem timing out and debugging is also very difficult.

I will give your solution a try, but if you have another solution i would appreciate if you can let me know.

thanks,

sam

 

[SamDeMan]

thanks a lot. i am now reworking my queries and consilidating them a bit. however, i think that it would be nice to add multiple recordsets to a report. (obviously not in a subreport)

sam