Three year history on report - recordset per year

[SAm]

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

 

[Larry Linson]

Without knowing more detail, it's just a guess, but could you join the three
Queries in a fourth one that you use as the RecordSource, so that the
corresponding values for each time period are brought together in the same
Record?

Larry Linson
Microsoft Access MVP

 

[SAm]

Thanks for your reply. However, this is my situation.

Each date range must be split up date with an if statement. basically my
data is not based by calendar day, it is by payperiod. this means that 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

 

[Larry Linson]

Will all of each year's data fit in one column on one page? If so, you may
be able to accomplish what you want with "long, skinny" subreports.

If the dates of the pay periods are different, how do you relate them for
comparison? If by the pay period number, and you can calculate that, that
may be the field on which you'd join the different years' data. It may also
be useful to consider creating a table of dates/pay period numbers, so you
can do a lookup instead of an on-the-fly calculation.

Larry Linson
Microsoft Access MVP