Results 1 to 8 of 8
  1. #1
    ABFNK is offline Novice
    Windows 8 Access 2010 64bit
    Join Date
    May 2015
    Posts
    2

    Formula for counting sub-totals within a report


    I am young to Access and trying to build a sales report. Essentially, I have groups by state and customer with customer details plus a grand total (report footer). What I am trying to count is the number of customers within a state but due to the customer details I cannot use the number of lines to count the data. What I want to count is the sub-totals of each customer within a state to get the number of customers in the state (this for state footer). Also would like to count in the report footer the total number of customers (this for the report footer). Any help that members can provide will be most appreciated. Thanks.

  2. #2
    June7's Avatar
    June7 is online now VIP
    Windows 7 64bit Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,816
    What table has the data? Build an aggregate GROUP BY (Totals) query of that table that returns unique customer IDs for each state in the same period that the report involves. Then a domain aggregate function expression in report can pull a count on that query.

    =DCount("*", "query name", "State='" & [State] & "'")

    Or possibly a nested subquery in the form's RecordSource can return the count by state.
    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.

  3. #3
    ABFNK is offline Novice
    Windows 8 Access 2010 64bit
    Join Date
    May 2015
    Posts
    2
    The data is taken from cross-tab query to filter to get the information I require (data by year), ex. acct. no., customer name, city, state, products (rows) and then sales by year for 5 years (columns). I then have built a report and deriving all the extra data from formulas. All worked perfectly except for counting the customers. All of the totals were built in the report. More ideas from you most welcome.

  4. #4
    June7's Avatar
    June7 is online now VIP
    Windows 7 64bit Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,816
    Advice is essentially the same.

    If you want the count of customers by state for each year, then adjust the aggregate query and the DCount() to handle. This might mean 5 textboxes in footer section, 1 for each year.
    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
    IrogSinta is offline Competent Performer
    Windows 8 Access 2013
    Join Date
    Jun 2015
    Posts
    103
    Just add a calculated textbox in both the State footer and Report footer with the following expression:
    =Count([NameOfCustomerField])

  6. #6
    June7's Avatar
    June7 is online now VIP
    Windows 7 64bit Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,816
    IrogSinta, that won't really work because customers can have more than one record. The Count will count the records, not unique customers. The OP has already recognized this by comments given in first post.
    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.

  7. #7
    IrogSinta is offline Competent Performer
    Windows 8 Access 2013
    Join Date
    Jun 2015
    Posts
    103
    In that case, add a textbox to Customer header group with the following expression as its ControlSource:
    =1
    Set its RunningSum property to OverGroup, its Visible property to No and give it a name such as txtCustomerCount.
    Then in your State footer, add a textbox with this in its ControlSource:
    =txtCustomerCount

  8. #8
    June7's Avatar
    June7 is online now VIP
    Windows 7 64bit Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,816
    Yes, that does work. Now that you describe it, seem to remember seeing this technique before. Wish I had remembered it.
    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. Replies: 8
    Last Post: 05-29-2015, 11:52 AM
  2. Replies: 1
    Last Post: 06-09-2012, 05:27 PM
  3. Formula for counting shifts
    By Huddle in forum Access
    Replies: 4
    Last Post: 05-10-2012, 02:56 PM
  4. Replies: 5
    Last Post: 12-06-2011, 11:18 AM
  5. Replies: 2
    Last Post: 08-25-2010, 01:42 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