Results 1 to 8 of 8
  1. #1
    Lounger
    Join Date
    Jul 2005
    Location
    Pennsylvania, USA
    Posts
    29
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Report Subtotal (Access 2003)

    There is probably an easy answer to this but as I'm more of a systems guy then an applications guy, and I'm new to Access, this is vexing me.

    I have a query driven report that groups data by date, and a few text fields in the detail section, and totals an amount field in the footer. I would like to total this amount field within each group for only records with a certain value of one of the text fields within the group (see attachment). The query has a date range parameter so the DSum function won't work.

    Can this issue be solved within Access or will it require some VB functionality?


    Richard

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Report Subtotal (Access 2003)

    If you want to total Hours Worked for records with JON = "00LEAV", you can use a text box with Control Source
    <code>
    =-Sum([Hours Worked]*([JON]="00LEAV"))
    </code>
    or
    <code>
    =Sum(IIf([JON]="00LEAV",[Hours Worked]"))
    </code>
    Substitute the correct field names if necessary.

  3. #3
    Lounger
    Join Date
    Jul 2005
    Location
    Pennsylvania, USA
    Posts
    29
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Report Subtotal (Access 2003)

    Hans -

    Thanks tons, it worked. I used the second option. You solved in 10 minutes what I've been stewing over, off and on, for a day!

    I've yet to figure out how you can possibly contribute the correct answer to just about every question, in every section, in such a short period of time. Just how many clones of you are there?

    I have one other question on topic. If I wanted to include an additional JON or JONs in the selection, what is the logic?

    Thanks again for your help!

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Report Subtotal (Access 2003)

    You can expand the condition in the IIf function:

    =Sum(IIf([JON]="00LEAV" Or [JON]="12ELSE",[Hours Worked]"))

    Note that you have to repeat the [JON]= for each condition.

  5. #5
    Lounger
    Join Date
    Jul 2005
    Location
    Pennsylvania, USA
    Posts
    29
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Report Subtotal (Access 2003)

    Many thanks to you Hans.

    Richard

  6. #6
    Super Moderator WebGenii's Avatar
    Join Date
    Jan 2001
    Location
    Redcliff, Alberta, Canada
    Posts
    4,066
    Thanks
    2
    Thanked 5 Times in 5 Posts

    Re: Report Subtotal (Access 2003)

    I've yet to figure out how you can possibly contribute the correct answer to just about every question, in every section, in such a short period of time. Just how many clones of you are there?
    Its a question we all wonder about <img src=/S/grin.gif border=0 alt=grin width=15 height=15>
    [b]Catharine Richardson (WebGenii)
    WebGenii Home Page
    Moderator: Spreadsheets, Other MS Apps, Presentation Apps, Visual Basic for Apps, Windows Mobile

  7. #7
    Platinum Lounger
    Join Date
    Dec 2001
    Location
    Melbourne, Australia
    Posts
    4,594
    Thanks
    0
    Thanked 27 Times in 27 Posts

    Re: Report Subtotal (Access 2003)

    I think you are wrong about the 10 minutes, in truth it probably took him a few seconds.

  8. #8
    Lounger
    Join Date
    Jul 2005
    Location
    Pennsylvania, USA
    Posts
    29
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Report Subtotal (Access 2003)

    Oh... no doubt! That 10 minutes I mentioned was in reference to the turn around time from when I posted the question. I'm sure a check of the logfiles for this site would show that Hans solved 2 or 3 puzzles in the Puzzle section and answered an additional question or 2 for someone else in between those actual 15 minutes that it took him to respond and answer mine.

    He's a real machine.

Posting Permissions

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