Results 1 to 4 of 4
  1. #1
    Join Date
    Sep 2007
    Posts
    2

    Unanswered: Calculate Data between two dates

    A newbie and I have learned a lot by reading posts but cannot find this one. I have a database with several yes/no checkboxes ie, referredtoRn,Followup,phoneCall, etc. I can calculate how many yes boxes are in each field - I researched and found that answer. However I want a report of how many in each field for between this date and this date such as a monthly report. Thanks in advance.

  2. #2
    Join Date
    Oct 2002
    Location
    Baghdad, Iraq
    Posts
    697
    Quote Originally Posted by oscarpoo
    A newbie and I have learned a lot by reading posts but cannot find this one. I have a database with several yes/no checkboxes ie, referredtoRn,Followup,phoneCall, etc. I can calculate how many yes boxes are in each field - I researched and found that answer. However I want a report of how many in each field for between this date and this date such as a monthly report. Thanks in advance.
    Something like this would work as a subquery: (you could paste it into a field in the query design grid)

    SELECT COUNT(*) FROM someTable WHERE referredtoRn = true AND someDateField BETWEEN fromDate AND toDate

    If the BETWEEN operator doesn't work, try fromDate <= someDateFIeld AND someDateField <= toDate instead.

  3. #3
    Join Date
    Sep 2007
    Posts
    2
    Ok I have tried and can not figure it out. I am using a query Clinic Reports Query that refers to a table (Clinic Info) I have a DateModified field
    In each place of the Yes/No such as
    First grid - Count of ReferredToRN: Sum(IIf([ReferredToRN],1,0))
    Second Grid - Count of FollowUp: Sum(IIf([FollowUp],1,0))
    etc
    with total as expression. it works great. but as soon as I put in under Datemodified it gives me one line for each yes instead of one line for each with a total I think I am supposed to place this

    SELECT COUNT(*) FROM Clinic Info WHERE referredtoRn = true AND DateMotified BETWEEN Start Date AND End Date

    in an empty grid but it did not work. It keeps saying the syntax of the subquery in the expression is incorrect. Check the subquery syntax and enclose the subquery in parenthesis.

    Am I on the right track? Missing something or just totally confused at this point?

  4. #4
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    you can count all your values in one pass
    however Id suggest using sum, and only including true / false, or Boolean columns.

    the SQL is somehting like...
    select abs(sum(<mybooleancolumn01>)) as <myalias01>, abs(sum(<mybooleancolumn02>)) as <myalias02>,....
    abs(sum(<mybooleancolumnxx>)) as <myaliasxx>
    from <mytablename>
    where <maydatecolumn> between <mysepecifiedstartdate> and mysepecifiedenddate);

    ..this takes advantage of the fact that Access stores true as -1, and false as zero, it should go without saying that this will only work for as long as JET represents true as -1 and false as zero
    so summing the value of the column gives the (negative) number of occurances of that column, the abs converts its to positive

    HTH
    Last edited by healdem; 09-24-07 at 05:30.
    I'd rather be riding on the Tiger 800 or the Norton

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •