Results 1 to 10 of 10
  1. #1
    garprob is offline Novice
    Windows 11 Access 2007
    Join Date
    Jan 2023
    Posts
    4

    Newby trying to total yes vs no

    I am new to Access and am using Access 2007. I have a report pretty much put together but need help with one last detail. My report has 5 columns one of which is labeled "Type". I have the report grouped by "type" with subtotals at the end of each group and a grand total at the bottom. Another column is labeled "Approval" with a possible yes or no in each record. The report is not grouped by "Approval". I would like a total of yes's and no's to show up at the bottom. It there a way I can do this? Thanks for your help!

  2. #2
    CJ_London is online now VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    11,399
    sounds like pretty much the same question asked (and answered) here
    https://www.access-programmers.co.uk...eckbox.326490/

  3. #3
    garprob is offline Novice
    Windows 11 Access 2007
    Join Date
    Jan 2023
    Posts
    4
    Thanks, It does sound like that is a similar question. I have no doubt that the solution described in the responses would work. However, I have no idea what the proposed solutions are talking about. Perhaps I should find a forum directed toward individuals with more basic knowledge?

  4. #4
    June7's Avatar
    June7 is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,825
    Use an aggregate expression in textbox. If you already have subtotals and totals in footers, then you already know how to do this.

    Is Approval a Yes/No or Text type field?
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  5. #5
    garprob is offline Novice
    Windows 11 Access 2007
    Join Date
    Jan 2023
    Posts
    4
    I'm afraid I don't know what an aggregate expression is. I stumbled upon a wat to group and total what I already have by clicking on the "Group and sort" button up on the tool bar. That allowed me to total the "type" field. But I don't see anything there to will allow me to total just the yes's or just the no's in the Approval field.

  6. #6
    CJ_London is online now VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    11,399
    to use the example in the link i provided and assuming the Approval field is a yes/no field

    create two textboxes in the group footer labelled say 'Approved' and 'Not Approved'

    in the controlsource of the Approved textbox put
    = -sum([Approval])

    in the controlsource of the Not Approved textbox put

    =-sum(not [Approval])

    or if that doesn't work try

    =-sum([Approval]=false)

  7. #7
    June7's Avatar
    June7 is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,825
    Look at those textboxes in report design view for subtotal and total. Do you see aggregate function (Sum, Count, Avg, etc.) expressions? Google the topic.

    As CJ shows, expressions can have conditional criteria.

    Say you had a field Species with 3 values - Cat, Dog, Horse and another field Quantity.

    =Sum(IIf(Species = "Cat", Quantity, 0))
    =Sum(IIf(Species = "Dog", Quantity, 0))
    =Sum(IIf(Species = "Horse", Quantity, 0))
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  8. #8
    ssanfu is offline Master of Nothing
    Windows 10 Access 2010 32bit
    Join Date
    Sep 2010
    Location
    Anchorage, Alaska, USA
    Posts
    9,664
    Quote Originally Posted by garprob View Post
    .... My report has 5 columns one of which is labeled "Type". .....
    I am not sure if you just have a label where the caption is "Type" or you have a table with a field name of "Type".

    If "Type" is is a field name in a table, be aware that "Type" is a reserved word in Access and should not be used as an object name.

    See Problem names and reserved words in Access

    Also "Type" is not very descriptive... Type of what? PaymentType?? ExpenseType??


    Good luck with your project..

  9. #9
    garprob is offline Novice
    Windows 11 Access 2007
    Join Date
    Jan 2023
    Posts
    4
    Quote Originally Posted by June7 View Post
    Look at those textboxes in report design view for subtotal and total. Do you see aggregate function (Sum, Count, Avg, etc.) expressions? Google the topic.

    As CJ shows, expressions can have conditional criteria.

    Say you had a field Species with 3 values - Cat, Dog, Horse and another field Quantity.

    =Sum(IIf(Species = "Cat", Quantity, 0))
    =Sum(IIf(Species = "Dog", Quantity, 0))
    =Sum(IIf(Species = "Horse", Quantity, 0))

    Awesome! I copied your example and replaced Species with approval and Cat with Yes. Worked perfectly. All the other examples here gave me errors. Thanks again!

  10. #10
    June7's Avatar
    June7 is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,825
    If Approval is a yes/no field, CJ's examples should work.

    If you have "Yes" (just like that between quote marks) then field is text type.
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Combo Box Help VBA total newby help
    By kiamba in forum Access
    Replies: 6
    Last Post: 01-24-2022, 09:08 PM
  2. Using Access Newby
    By LearningAccessDB in forum Access
    Replies: 7
    Last Post: 04-02-2020, 11:06 AM
  3. Hello From A Newby
    By Wessex in forum Access
    Replies: 5
    Last Post: 08-14-2015, 11:42 AM
  4. Best Practice db development - newby to Access
    By CraigJ in forum Database Design
    Replies: 4
    Last Post: 04-15-2015, 08:11 AM
  5. Help! Newby to access - recurring events
    By stevo63 in forum Access
    Replies: 2
    Last Post: 06-27-2012, 06:29 PM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums