如何将DCount与记录集上的条件一起使用？
如何将DCount与记录集上的条件一起使用？
提问于 2019-08-16 16:26:04
我正在尝试构建一个查询，以统计网球运动员在某一特定比赛记录之前所打的比赛(记录)数量。以下是主要匹配表的示例：
+-----------------------+------------------------+-----------------------+------------+-------------------+-----------+
| ID1_G | ID2_G | ID_T_G | ID_R_G | RESULT_G | DATE_G |
+-----------------------+------------------------+-----------------------+------------+-------------------+-----------+
| Patrick Davidson | Darren K. Polkinghorne | Launceston Challenger | q-Second | 4-6 7-5 7-5 | 07-Feb-15 |
| Tennys Sandgren | David Barclay | Launceston Challenger | q-Second | 6-0 6-3 | 07-Feb-15 |
| Gavin Van Peperzeel | Alexander Klintcharov | Launceston Challenger | q-Second | 6-1 4-6 6-1 | 07-Feb-15 |
| Yuichi Sugita | Daniel Groom | Launceston Challenger | q-Second | 6-0 6-3 | 07-Feb-15 |
| Omar Jasika | Daniel Nolan | Launceston Challenger | q-Second | 6-1 6-4 | 07-Feb-15 |
| Finn Tearney | Christian Trubrig | Launceston Challenger | q-Second | 7-6(2) 6-0 | 07-Feb-15 |
| Alexander Sarkissian | Issei Okamura | Launceston Challenger | q-Second | 6-1 6-1 | 07-Feb-15 |
| Jacob Grills | Stefanos Tsitsipas | Launceston Challenger | q-Second | 6-4 1-6 6-0 | 07-Feb-15 |
| Finn Tearney | Alexander Sarkissian | Launceston Challenger | Qualifying | 6-4 5-7 6-4 | 08-Feb-15 |
| Yuichi Sugita | Tennys Sandgren | Launceston Challenger | Qualifying | 4-6 6-4 6-2 | 08-Feb-15 |
| Gavin Van Peperzeel | Patrick Davidson | Launceston Challenger | Qualifying | 6-3 7-6(2) | 08-Feb-15 |
| Omar Jasika | Jacob Grills | Launceston Challenger | Qualifying | 6-1 7-6(6) | 08-Feb-15 |
| Benjamin Mitchell | Maverick Banes | Launceston Challenger | First | 6-4 6-2 | 09-Feb-15 |
| Mitchell Krueger | Omar Jasika | Launceston Challenger | First | 6-3 4-6 6-3 | 09-Feb-15 |
| Ze Zhang | Alex Bolt | Launceston Challenger | First | 6-4 6-4 | 09-Feb-15 |
| Bjorn Fratangelo | Blake Mott | Launceston Challenger | First | 6-2 6-4 | 09-Feb-15 |
| Christopher O'Connell | Somdev Devvarman | Launceston Challenger | First | 6-2 6-1 | 09-Feb-15 |
| Brydan Klein | Sanam Singh | Launceston Challenger | First | 7-6(5) 6-7(2) 6-2 | 09-Feb-15 |
| Jordan Thompson | Yuuya Kibi | Launceston Challenger | First | 6-3 3-6 7-5 | 09-Feb-15 |
| Harry Bourchier | Hiroki Moriya | Launceston Challenger | First | 6-1 7-6(10) | 09-Feb-15 |
| Bradley Klahn | Matthew Ebden | Launceston Challenger | First | 6-0 6-4 | 10-Feb-15 |
| Hyeon Chung | Matt Reid | Launceston Challenger | First | 3-6 7-5 7-5 | 10-Feb-15 |
| Radu Albot | Di Wu | Launceston Challenger | First | 6-4 6-3 | 10-Feb-15 |
| Matthew Barton | Kyle Edmund | Launceston Challenger | First | 3-6 6-3 2-0 ret. | 10-Feb-15 |
| Dayne Kelly | Gavin Van Peperzeel | Launceston Challenger | First | 6-2 3-6 6-2 | 10-Feb-15 |
| Yuichi Sugita | Marc Polmans | Launceston Challenger | First | 6-4 4-6 6-3 | 10-Feb-15 |
| Jose Statham | Finn Tearney | Launceston Challenger | First | 7-5 6-3 | 10-Feb-15 |
| Luke Saville | Andrew Whittington | Launceston Challenger | First | 5-7 6-4 6-0 | 10-Feb-15 |
| Jordan Thompson | Luke Saville | Launceston Challenger | Second | 6-4 6-4 | 11-Feb-15 |
| Harry Bourchier | Brydan Klein | Launceston Challenger | Second | 2-6 7-6(5) 6-4 | 11-Feb-15 |
| Benjamin Mitchell | Christopher O'Connell | Launceston Challenger | Second | 3-6 6-3 6-4 | 11-Feb-15 |
| Hyeon Chung | Mitchell Krueger | Launceston Challenger | Second | 4-6 6-3 6-4 | 11-Feb-15 |
| Bradley Klahn | Dayne Kelly | Launceston Challenger | Second | 5-7 6-3 6-2 | 12-Feb-15 |
| Jose Statham | Radu Albot | Launceston Challenger | Second | 6-3 3-6 6-2 | 12-Feb-15 |
| Ze Zhang | Matthew Barton | Launceston Challenger | Second | 6-3 6-7(5) 7-6(5) | 12-Feb-15 |
| Bjorn Fratangelo | Yuichi Sugita | Launceston Challenger | Second | w/o | 12-Feb-15 |
| Jordan Thompson | Benjamin Mitchell | Launceston Challenger | 1/4 | 6-2 6-3 | 13-Feb-15 |
| Hyeon Chung | Harry Bourchier | Launceston Challenger | 1/4 | 6-0 3-6 6-1 | 13-Feb-15 |
| Bjorn Fratangelo | Bradley Klahn | Launceston Challenger | 1/4 | 7-6(2) 6-3 | 13-Feb-15 |
| Ze Zhang | Jose Statham | Launceston Challenger | 1/4 | 6-4 6-3 | 13-Feb-15 |
| Hyeon Chung | Jordan Thompson | Launceston Challenger | 1/2 | 5-7 6-3 7-5 | 14-Feb-15 |
| Bjorn Fratangelo | Ze Zhang | Launceston Challenger | 1/2 | 6-3 ret. | 14-Feb-15 |
| Bjorn Fratangelo | Hyeon Chung | Launceston Challenger | Final | 4-6 6-2 7-5 | 15-Feb-15 |
+-----------------------+------------------------+-----------------------+------------+-------------------+-----------+值得我指出的是，火柴表的结构不是由我决定的--这是一个第三方应用程序，我已经为它购买了许可证，无法改变。从上下文的角度看：ID1字段是赢家，ID2字段是失败者。因此，要计算ID1的比赛数量，就必须从ID1和ID2字段中计算以前的记录，因为在过去，它们都会赢得和输掉比赛。当然，ID2的反之亦然。
我正在寻找的输出是提取一些现有字段并添加两个“计数”字段ID1_CNT和ID2_CNT，如下所示：
+-----------+------------+-----------------------+-----------------------+------------------------+---------+---------+
| DATE_G | ID_R_G | ID_T_G | ID1_G | ID2_G | ID1_CNT | ID2_CNT |
+-----------+------------+-----------------------+-----------------------+------------------------+---------+---------+
| 07-Feb-15 | q-Second | Launceston Challenger | Patrick Davidson | Darren K. Polkinghorne | 8 | 14 |
| 07-Feb-15 | q-Second | Launceston Challenger | Tennys Sandgren | David Barclay | 109 | 6 |
| 07-Feb-15 | q-Second | Launceston Challenger | Gavin Van Peperzeel | Alexander Klintcharov | 18 | 7 |
| 07-Feb-15 | q-Second | Launceston Challenger | Yuichi Sugita | Daniel Groom | 351 | 1 |
| 07-Feb-15 | q-Second | Launceston Challenger | Omar Jasika | Daniel Nolan | 37 | 7 |
| 07-Feb-15 | q-Second | Launceston Challenger | Finn Tearney | Christian Trubrig | 9 | 1 |
| 07-Feb-15 | q-Second | Launceston Challenger | Alexander Sarkissian | Issei Okamura | 7 | 9 |
| 07-Feb-15 | q-Second | Launceston Challenger | Jacob Grills | Stefanos Tsitsipas | 25 | 8 |
| 08-Feb-15 | Qualifying | Launceston Challenger | Finn Tearney | Alexander Sarkissian | 10 | 8 |
| 08-Feb-15 | Qualifying | Launceston Challenger | Yuichi Sugita | Tennys Sandgren | 352 | 110 |
| 08-Feb-15 | Qualifying | Launceston Challenger | Gavin Van Peperzeel | Patrick Davidson | 19 | 9 |
| 08-Feb-15 | Qualifying | Launceston Challenger | Omar Jasika | Jacob Grills | 38 | 26 |
| 09-Feb-15 | First | Launceston Challenger | Bjorn Fratangelo | Blake Mott | 74 | 32 |
| 09-Feb-15 | First | Launceston Challenger | Ze Zhang | Alex Bolt | 174 | 68 |
| 09-Feb-15 | First | Launceston Challenger | Benjamin Mitchell | Maverick Banes | 129 | 30 |
| 09-Feb-15 | First | Launceston Challenger | Jordan Thompson | Yuuya Kibi | 65 | 27 |
| 09-Feb-15 | First | Launceston Challenger | Harry Bourchier | Hiroki Moriya | 29 | 225 |
| 09-Feb-15 | First | Launceston Challenger | Mitchell Krueger | Omar Jasika | 87 | 39 |
| 09-Feb-15 | First | Launceston Challenger | Brydan Klein | Sanam Singh | 194 | 102 |
| 09-Feb-15 | First | Launceston Challenger | Christopher O'Connell | Somdev Devvarman | 20 | 365 |
| 10-Feb-15 | First | Launceston Challenger | Jose Statham | Finn Tearney | 138 | 11 |
| 10-Feb-15 | First | Launceston Challenger | Yuichi Sugita | Marc Polmans | 353 | 24 |
| 10-Feb-15 | First | Launceston Challenger | Bradley Klahn | Matthew Ebden | 191 | 334 |
| 10-Feb-15 | First | Launceston Challenger | Dayne Kelly | Gavin Van Peperzeel | 42 | 20 |
| 10-Feb-15 | First | Launceston Challenger | Matthew Barton | Kyle Edmund | 67 | 107 |
| 10-Feb-15 | First | Launceston Challenger | Radu Albot | Di Wu | 209 | 147 |
| 10-Feb-15 | First | Launceston Challenger | Luke Saville | Andrew Whittington | 104 | 47 |
| 10-Feb-15 | First | Launceston Challenger | Hyeon Chung | Matt Reid | 83 | 183 |
| 11-Feb-15 | Second | Launceston Challenger | Jordan Thompson | Luke Saville | 66 | 105 |
| 11-Feb-15 | Second | Launceston Challenger | Harry Bourchier | Brydan Klein | 30 | 195 |
| 11-Feb-15 | Second | Launceston Challenger | Hyeon Chung | Mitchell Krueger | 84 | 88 |
| 11-Feb-15 | Second | Launceston Challenger | Benjamin Mitchell | Christopher O'Connell | 130 | 21 |
| 12-Feb-15 | Second | Launceston Challenger | Jose Statham | Radu Albot | 139 | 210 |
| 12-Feb-15 | Second | Launceston Challenger | Bjorn Fratangelo | Yuichi Sugita | 75 | 354 |
| 12-Feb-15 | Second | Launceston Challenger | Ze Zhang | Matthew Barton | 175 | 68 |
| 12-Feb-15 | Second | Launceston Challenger | Bradley Klahn | Dayne Kelly | 192 | 43 |
| 13-Feb-15 | 1/4 | Launceston Challenger | Ze Zhang | Jose Statham | 176 | 140 |
| 13-Feb-15 | 1/4 | Launceston Challenger | Bjorn Fratangelo | Bradley Klahn | 76 | 193 |
| 13-Feb-15 | 1/4 | Launceston Challenger | Jordan Thompson | Benjamin Mitchell | 67 | 131 |
| 14-Feb-15 | 1/2 | Launceston Challenger | Hyeon Chung | Jordan Thompson | 86 | 68 |
| 14-Feb-15 | 1/2 | Launceston Challenger | Bjorn Fratangelo | Ze Zhang | 77 | 177 |
| 15-Feb-15 | Final | Launceston Challenger | Bjorn Fratangelo | Hyeon Chung | 78 | 87 |
+-----------+------------+-----------------------+-----------------------+------------------------+---------+---------+计数比示例数据要大，因为它们计算了整个表，但是您可以看到随着玩家的比赛，计数是如何增加的。
以上内容来自SQL，如下所示：
SELECT games_atp.DATE_G, games_atp.ID_R_G, games_atp.ID_T_G, games_atp.ID1_G, games_atp.ID2_G, (SELECT COUNT(Dupe.ID1_G)
FROM games_atp as Dupe
WHERE Dupe.ID1_G = games_atp.ID1_G
AND Dupe.DATE_G < games_atp.DATE_G)+(SELECT COUNT(Dupe.ID2_G)
FROM games_atp as Dupe
WHERE Dupe.ID2_G = games_atp.ID1_G
AND Dupe.DATE_G < games_atp.DATE_G) AS ID1_CNT, (SELECT COUNT(Dupe.ID2_G)
FROM games_atp as Dupe
WHERE Dupe.ID2_G = games_atp.ID2_G
AND Dupe.DATE_G < games_atp.DATE_G)+(SELECT COUNT(Dupe.ID1_G)
FROM games_atp as Dupe
WHERE Dupe.ID1_G = games_atp.ID2_G
AND Dupe.DATE_G < games_atp.DATE_G) AS ID2_CNT
FROM games_atp
ORDER BY games_atp.DATE_G;然而，这是非常缓慢的，甚至几个小时之后，访问也没有显示出任何结果。在games_atp中有大约280 K的记录。为了确保没有任何错误，我构建了一批几个记录(上面的示例数据集)来运行这个记录，它工作了几秒钟，却花了几秒钟的时间来完成一份完整的记录。因此，看起来运行这种计数只需要一个年龄(或者结构错误？)。
在Excel中，我使用数组来执行大型计算，因为这些数据显然存储在内存中，因此运行得更快(我是这方面的新手)。我开始阅读更多关于Access中的数组的内容，并发现了也可以存储在内存中的记录集。到目前为止，我已经开始使用VBA，创建了一个记录集，并且我尝试使用DCount函数来用标准来计数记录。问题是，当我将记录集设置为域时，Access似乎并不喜欢它。
所以有几个问题：
- 是否可以使用以记录集作为域的条件来使用DCount？
- 我是个白痴，我还没有找到更简单的解决办法吗？
提前谢谢。
回答 1
Stack Overflow用户
回答已采纳
发布于 2019-08-16 22:17:30
如果您想计数每个球员的比赛(无论胜负)，那么不需要两个计数字段。配对球员与比赛计数无关。
使用UNION查询将表数据重新排列为规范化结构：
SELECT Date_G, ID_R_G, ID_T_G, ID1_G AS PlayerName, "Winner" AS Outcome FROM Matches
UNION SELECT Date_G, ID_R_G, ID_T_G, ID2_G, "Loser" FROM Matches;然后，要在当前记录日期之前获得比赛的运行计数：
SELECT *, (SELECT Count(*) FROM qryUNION
WHERE qryUNION.PlayerName=T1.PlayerName AND qryUNION.Date_G<T1.Date_G) AS Total
FROM qryUNION AS T1 ORDER BY PlayerName, Date_G;若要在计数中包括当前记录，请将<更改为<=。
但是，在查询中运行calcs可能在大型数据集中执行得非常慢，基于UNION数据集的查询无助于提高性能。VBA方法可能更快。https://www.tek-tips.com/viewthread.cfm?qid=1532770的一个例子。
为每一名选手计算胜负：
TRANSFORM Count(qryUNION.Date_G) AS CountOfDate_G
SELECT qryUNION.PlayerName
FROM qryUNION
GROUP BY qryUNION.PlayerName
PIVOT qryUNION.Outcome;返回每个球员的比赛总数(每名球员1次记录)：
SELECT PlayerName, Count(*) AS CountMatches
FROM qryUNION
GROUP BY PlayerName;然后，如果您真的想要在两列中显示匹配计数(而不是运行)，则为播放器对：
SELECT Matches.*, Query1.CountMatches, Matches.ID2_G, Query1_1.CountMatches
FROM Query1 AS Query1_1
INNER JOIN (Query1 INNER JOIN Matches ON Query1.PlayerName = Matches.ID1_G)
ON Query1_1.PlayerName = Matches.ID2_G;页面原文内容由Stack Overflow提供。腾讯云小微IT领域专用引擎提供翻译支持
原文链接：
https://stackoverflow.com/questions/57528099
复制相关文章
相似问题
腾讯云开发者
