Results 1 to 3 of 3
  1. #1
    Silver Lounger
    Join Date
    Jan 2001
    Location
    Northern, California, USA
    Posts
    1,886
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Access Reporting: Counting Formulas

    Can someone give me some details on the exact formula I would use in a report field to give me the total number of records in [status] field, where the status is "complete"?

    Also, how can I get a 'rolling total' of sorts? I'd like the total number of records where the status is "Complete" for the last 7 days, 30 days, and total for this month, and this year only.

    If it seems like i'm trying to get you all to do my work, you're somewhat right, I'm just not that familiar with reporting calculations in Access just yet.

    Thanks for teaching! <img src=/S/wink.gif border=0 alt=wink width=15 height=15> <img src=/S/smile.gif border=0 alt=smile width=15 height=15>
    <IMG SRC=http://www.wopr.com/w3tuserpics/Kel_sig.gif>
    Moderator:<font color=448800> Pix Place, Internet Explorer</font color=448800>
    <small>www.kvisions.com

  2. #2
    Silver Lounger
    Join Date
    Jan 2001
    Location
    Indianapolis, Indiana, USA
    Posts
    1,862
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Access Reporting: Counting Formulas

    Hi (again) Drk,
    You'll want to use a DCount() to count the number of instances where the [status] is complete. Something like:<pre>=DCOUNT("[status]","<YourTable/QueryName>","[status]='complete'")</pre>

    As for a rolling total, you can use a regular Count function in a textbox, but set the Running Sum property to "Over All" or "Over Group" (depending on whether you want to keep tally over a Section of your report or the whole thing).

    HTH <img src=/S/thumbup.gif border=0 alt=thumbup width=15 height=15>

  3. #3
    TracyF
    Guest

    Re: Access Reporting: Counting Formulas

    An alternative to the DCount is to create a field in the query that the report is built on, CountThis:iif([Status] = "Complete",1,0), and then use the Sum function on the CountThis field.

Posting Permissions

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