Daily counts

[Ed Robichaud]

I'm struggling to come up with a query/report of daily attendance for
records that have only start and stop dates. A x-tab (with parameters that
records be within a selected date range) would seem to be the way to go
here, but how do I get a count for each date within that range?

thanks -Ed

[Pachydermitis]

On May 12, 9:24*am, "Ed Robichaud" <[e-mail]:

I'm struggling to come up with a query/report of daily attendance for
records that have only start and stop dates. *A x-tab (with parameters that
records be within a selected date range) * would seem to be the way to go
here, but how do I get a count for each date within that range?

thanks -Ed

Try using datediff in the query field.
Without seeing an example of your table I cn't be sure, but you might
put a conditional (datediff('d',FieldDate,date())=1) then sum instead
of count
P

[Ed Robichaud]

Thanks, I'm familiar with the DateDiff function, but what I would really
like is a method to calculate the # of folks present for each day within a
user selected date range.

The date-filtered query now returns records with:
ID, startdate, stopdate, etc.

i.e. 001; 3/2/08; 3/20/08;...
002; 3/1/08; 3/18/08;...
003; 03/15/08;3/20/08;...

For example, I can find how many folks were present sometime within
3/1/08-3/31-08, but I'd like a daily count of those present for:

3/1/08
3/2/08
3/308
....
3/31/08

-Ed

"Pachydermitis" <pr*******@gmail.comwrote in message
news:fc**********************************@w8g2000p rd.googlegroups.com...
On May 12, 9:24 am, "Ed Robichaud" <[e-mail]:

I'm struggling to come up with a query/report of daily attendance for
records that have only start and stop dates. A x-tab (with parameters that
records be within a selected date range) would seem to be the way to go
here, but how do I get a count for each date within that range?

thanks -Ed

Try using datediff in the query field.
Without seeing an example of your table I cn't be sure, but you might
put a conditional (datediff('d',FieldDate,date())=1) then sum instead
of count
P

[rquintal]

On May 12, 12:24*pm, "Ed Robichaud" <[e-mail]:

I'm struggling to come up with a query/report of daily attendance for
records that have only start and stop dates. *A x-tab (with parameters that
records be within a selected date range) * would seem to be the way to go
here, but how do I get a count for each date within that range?

thanks -Ed

Create a calculated field in the query that determines if the date
falls between the start and stop dates, returning the number 1 if
true, and 0 if false, then sum those numbers.

isPresent: IIF([Enter Date]>=[Start] AND [Enter Date]<=[stop],1,0)

Q