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
