Daily counts

Collapse
This topic is closed.
X
X
Collapse
 
  • Page of 1
  • Time
  • Show
Clear All
new posts
Previous template Next
  • Ed Robichaud
    #1

    Daily counts

    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


    Tags: None
    • Pachydermitis
      #2
      Re: Daily counts

      On May 12, 9:24 am, "Ed Robichaud" <edrobich...@wd n.comwrote:
      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',F ieldDate,date() )=1) then sum instead
      of count
      P

        Comment

        • Ed Robichaud
          #3
          Re: Daily counts

          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

          "Pachydermi tis" <praesagus@gmai l.comwrote in message
          news:fc2d239e-7388-47b1-9150-d75541669337@w8 g2000prd.google groups.com...
          On May 12, 9:24 am, "Ed Robichaud" <edrobich...@wd n.comwrote:
          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',F ieldDate,date() )=1) then sum instead
          of count
          P


            Comment

            • rquintal@sympatico.ca
              #4
              Re: Daily counts

              On May 12, 12:24 pm, "Ed Robichaud" <edrobich...@wd n.comwrote:
              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

                Comment

                Previous template Next
                Working...