Results 1 to 6 of 6
  1. #1
    vinsavant is offline Novice
    Windows Vista Access 2010 64bit
    Join Date
    Dec 2012
    Posts
    20

    Distinct Count of Records on an Access Report

    Hello,



    I have created a report in Access that has the following structure:
    Click image for larger version. 

Name:	Sample Report.jpg 
Views:	12 
Size:	83.4 KB 
ID:	32098

    While I can create a simple record count of the Application ID and place it in the Region section, I am struggling to create one the would give me a distinct or unique count of applications within the Region. In the above example of a total of 3 applications since Canada and Mexico use the same type of application, in this case, MS Word.

    It has been suggested that I create a query. If so, how is that done? Do I create an unbound field in the Region Heading of the report and paste the SQL statement inside? Please provide a bit more detail and a sample query syntax if possible.

    Thank you!

    Last edited by vinsavant; 01-13-2018 at 10:55 AM. Reason: Additional feedback

  2. #2
    June7's Avatar
    June7 is online now VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,825
    Have to do a query that returns DISTINCT applications for each region. Then use that query in another query that counts the applications by region.
    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
    vinsavant is offline Novice
    Windows Vista Access 2010 64bit
    Join Date
    Dec 2012
    Posts
    20
    Thank you for your reply. Once I create that outside query, do I then create an unbound field in the Region Heading of the report and paste the SQL statement inside? Please provide a bit more detail and a sample query syntax if possible. Thanks!

  4. #4
    orange's Avatar
    orange is offline Moderator
    Windows 10 Access 2010 32bit
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    16,716
    You should be aware that Reports have their own Sorting and Grouping facilities which can override statements in the record source of the report.
    Here's an article that may help generally. You may want to search and watch some youtube videos that deal with sorting, grouping etc. of Access reports.

  5. #5
    June7's Avatar
    June7 is online now VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,825
    Query1
    SELECT DISTINCT Region, ApplicationID FROM table;
    or
    SELECT Region, ApplicationID FROM table GROUP BY Region, ApplicationID;

    Now options are:

    1. build Query2
    SELECT Region, Count(ApplicationID) AS CountID FROM Query1 GROUP BY Region;
    and in another query used as report RecordSource join Query2 to the original table linking on the Region fields

    2. use DCount() domain aggregate function expression in textbox: =DCount("ApplicationID", "Query1", "Region='" & [Region] & "'")


    Note the removal of all spaces in field names. Should not use spaces nor punctuation/special characters (underscore only exception) in naming convention.
    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.

  6. #6
    isladogs's Avatar
    isladogs is offline MVP / VIP
    Windows 10 Access 2010 32bit
    Join Date
    Jan 2014
    Location
    Somerset, UK
    Posts
    5,954
    Colin, Access MVP, Website, email
    The more I learn, the more I know I don't know. When I don't know, I keep quiet!
    If I don't know that I don't know, I don't know whether to answer

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

Similar Threads

  1. Distinct Count
    By ertweety in forum Queries
    Replies: 1
    Last Post: 04-15-2014, 10:10 AM
  2. Count Distinct in Access
    By georgerudy in forum Access
    Replies: 1
    Last Post: 11-28-2010, 01:24 PM
  3. Access DB distinct count
    By anziga in forum Queries
    Replies: 3
    Last Post: 10-12-2010, 02:20 PM
  4. Count distinct records in parameterized query
    By SilverSN95 in forum Access
    Replies: 5
    Last Post: 07-27-2010, 09:31 AM
  5. Replies: 0
    Last Post: 08-08-2008, 08:34 AM

Tags for this Thread

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