Sum of dates in a large query

[walkerhunter]

All,
Here's the situation. I have a query with 35 columns containing dates. I need run the query and then somehow find to the total number of dates up until a certain point. For example, each date is a visit, so I need to total the number of visits that have occurred before, say, 12/31/01. I am thinking that the easiest way to do this would be to export the query to excel, highlight the range, and then count everything <= 12/31/01. But it would be nice to keep it Access as a query that a novice user could run. Anyone how of a function? Thanks for the help.

PS. I know what your thinking, why 35 columns of visits? The &quot;visits&quot; table had to be de-normalized to accommodate visit projections. Long story, but this is how the table stands. Thanks for the help.

 

[MichaelRed]

&quot; ... The &quot;visits&quot; table had to be de-normalized to accommodate visit projections ... &quot;

WRONG!!!!!!!!!!!!!!!!

And NOW, you pay the price.

IF I were invloved, I would (after wringing a neck or two), generate a crosstab (or pivot) query and then just do a count on the columns, with the criteria.

MichaelRed
m.red@att.net

There is never time to do it right but there is always time to do it over

 

[braindead2]

you can use a query with a massive where \or statement or you could attempt a huge Union query but you can probably do this by navigatiging a recordset

need something like below: (typed on the fly so will not work without serious editing) just to give you idea

dim rst as recordset
dim x as integer
dim cnt as integer
set rst = me.recordsetclone 'open recordset
rst.movefirst
do until rst.eof
for x = 0 to 35 'set counter to go thru each date field
if rst.fields.field(x) <= #12/31/01# then cnt = cnt + 1
next x
rst.movenext
loop
rst.close
set rst = nothing
debug.print &quot;total matches &quot; & cnt

 

[walkerhunter]

will try, thanks for both the suggestions, even the harsh response above. I didn't create this table, but the guy above me had some reasons for doing so. He's as much against denormalizing tables as the next person. Thanks again.