CODEWITHSUNDEEP
ms-access
Jossy
Jossy

Reputation: 989

How do I use DCount with criteria on a Recordset?

I'm trying to build a query that counts the number of matches (records) a tennis player has played prior to a particular match record. Here's a sample of the main Matches table:

+-----------------------+------------------------+-----------------------+------------+-------------------+-----------+
|         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 |
+-----------------------+------------------------+-----------------------+------------+-------------------+-----------+

Worth me pointing out that the Matches table structure was not determined by me - it's a third party app that I've bought a licence for and can't change. By way of context: the ID1 field is the winner and ID2 is the loser. Therefore to count the number of matches for ID1 then you have to count the previous records from both the ID1 and ID2 fields because in the past they will have both won and lost matches. Vice versa for ID2 of course.

The output I'm looking for is to pull some existing fields and add in two 'count' fields ID1_CNT and ID2_CNT as follows:

+-----------+------------+-----------------------+-----------------------+------------------------+---------+---------+
|  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 |
+-----------+------------+-----------------------+-----------------------+------------------------+---------+---------+

The counts are greater than the sample data because they count the whole table but you can see how the counts increase as a player plays matches.

The above comes from the SQL as below: 

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;

This was incredibly slow though and even after several hours Access was showing no sign of a result. There are circa 280k records in games_atp. To make sure nothing was wrong I built a batch of a few records (the sample dataset above) to run this on and it worked but took a few seconds for a handfull of records. So it looks like it just takes an age to run this sort of count (or the structure is wrong?).

In Excel I used arrays to perform big calculations as these were apparently stored in memory so ran faster (I'm a newbie at this). I started reading more about arrays in Access and discovered Recordsets which can also be stored in memory. So far I've swtiched into VBA, created a Recordset and I'm trying to use DCount function to count records with criteria. The issue is that Access doesn't seem to like it when I set the Recordset as the Domain.

So couple of questions:

  1. Is it possible to use DCount with criteria with a Recordset as the Domain?
  2. Am I being an idiot and there's a much simpler solution to this I haven't found?

Thanks in advance.

Upvotes: 2

Views: 735

Answers (1)

June7
June7

Reputation: 21370

If you want to count matches (regardless of win or loss) per player, then don't need two count fields. Paired players is not relevant to match count.

Rearrange Matches table data to normalized structure with a UNION query:

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;

Then to get running count of matches prior to current record date:

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;

To include current record in the count, change < to <=.

However, running calcs in query can perform very slow on large datasets and basing query on a UNION dataset doesn't help improve performance. VBA approach might be faster. An example at https://www.tek-tips.com/viewthread.cfm?qid=1532770.

To count wins and losses for each player:

TRANSFORM Count(qryUNION.Date_G) AS CountOfDate_G
SELECT qryUNION.PlayerName
FROM qryUNION
GROUP BY qryUNION.PlayerName
PIVOT qryUNION.Outcome;

To return total matches for each player (1 record per player):

SELECT PlayerName, Count(*) AS CountMatches 
FROM qryUNION 
GROUP BY PlayerName;

Then if you really want to show match counts (not running) in two columns for player pairs: 

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;

Upvotes: 1

Related Questions