挑战群组标题中不同的记录数 - 访问报告 [英] Challenging Distinct Record Count in Group Heading - Access Report

问题描述

你好，



我在Access中创建了一个具有以下结构的报告:(请参阅随附的样本报告图片）

虽然我可以创建一个简单的应用程序ID记录计数并将其放在Region部分，但我很难创建
一个会给我一个独特或唯一的应用程序计数区域。在上面的例子中，自加拿大和墨西哥以来共有3个申请使用相同类型的申请，在这种情况下，MS Word。

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.

有人建议我创建一个查询。如果是这样，那怎么办？我在
区域标题中创建一个未绑定的字段报告并将SQL语句粘贴到里面？如果可能，请提供更多详细信息和示例查询语法。 



谢谢！

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!

推荐答案

要计算不同的值，您需要使用子查询返回不同的应用程序ID值，并计算每个区域子查询返回的行。 您没有给出数据库模型的任何指示，因此我假设您有一个Region
ID列一个Countries表，引用Regions表的主键，以及Applications表中的Country ID列，在这种情况下，查询将是：
$


SELECT [Region ID]，COUNT（*）AS ApplicationCount

FROM（SELECT DISTINCT [地区ID]，[应用程序ID]

              FROM Applications INNER JOIN国家/
             ON应用程序。[CountryID] =国家/地区，[国家/地区ID]）

GROUP BY [地区ID];



在报告的RecordSource查询中，您可以将上述查询加入Region ID上的Regions表列并将组头中的控件绑定到ApplicationCount列。



可以通过将相关子查询添加到报表的当前查询来完成，但我认为你'我会发现如上所述更容易创建一个单独的查询并将其加入当前查询。



另一种解决方案是在组头中使用未绑定的控件，并通过调用控件的ControlSource属性中的DLookup函数，从上面的查询中查找当前Region ID的ApplicationCount值。

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.

这篇关于挑战群组标题中不同的记录数 - 访问报告的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！