Challenging Distinct Record Count in Group Heading - Access Report RRS feed

  • Question

  • Hello,

    I have created a report in Access that has the following structure: (Please see attached sample report image)

    Region      Country Business Entity Name Application Name Application ID
    ABC Business  MS Word 0001
    YXT Business MS Excel 0002

    US SYU Business MS Access 0003

    Mexico PHY Business Ms Word 0001

    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!

    • Edited by Mmunozjr Sunday, January 14, 2018 5:07 AM
    Sunday, January 14, 2018 4:27 AM

All replies

  • To count distinct values you need to use a subquery which returns the distinct Application ID values, and count the rows returned by the subquery per region.  You don't give any indication of the database's model, so I'll assume you have a Region ID column in a Countries table, referencing the primary key of a Regions table, and a Country ID column in an Applications table, in which case the query would be:

    SELECT [Region ID], COUNT(*) AS ApplicationCount
    FROM (SELECT DISTINCT [Region ID], [Application ID]
                 FROM Applications INNER JOIN Countries
                 ON Applications.[CountryID] = Countries,[Country ID])
    GROUP BY [Region ID];

    In the report's RecordSource query you could then join the above query to the Regions table on the Region ID column and bind a control in the group header to the ApplicationCount column.

    It could all be done by adding correlated subqueries to the report's current query, but I think you'll find it easier to create a separate query as above and join it in the current query.

    And alternative solution would be to use an unbound control in the group header, and look up the ApplicationCount value for the current Region ID from the above query by calling the DLookup function in the control's ControlSource property.

    Ken Sheridan, Stafford, England

    • Edited by Ken Sheridan Sunday, January 14, 2018 1:07 PM Typo corrected.
    • Proposed as answer by Terry Xu - MSFT Monday, January 15, 2018 7:15 AM
    Sunday, January 14, 2018 1:05 PM
  • Cross posted at https://www.access-programmers.co.uk/forums/newreply.php?do=newreply&noquote=1&p=1562088

    Sunday, January 14, 2018 8:47 PM