I am looking for a way to have a query give counts for records containing the same field value. In the example below, I need to have one entry for the ClientNumber with the total count of occurances of this field value. It's most likely pretty basic but it's been a few years since I've worked with Access.
Here is the SQL code so far:
SELECT DISTINCT ClientVisits.ClientNumber, ClientVisits.DateofVisit, Count(ClientVisits.ClientNumber) AS CountOfClientNumber
FROM ClientVisits
GROUP BY ClientVisits.ClientNumber, ClientVisits.DateofVisit
HAVING (((ClientVisits.DateofVisit)>=#12/1/2022# And (ClientVisits.DateofVisit)<=#12/31/2022#) AND ((Count(ClientVisits.ClientNumber))>0))
ORDER BY ClientVisits.DateofVisit;
Thanks,
Steve
ClientNumber DateofVisit CountOfClientNumber 1001 12/17/2022 10:22:04 AM 1 1001 12/3/2022 10:04:55 AM 1 1005 12/15/2022 9:03:43 AM 1 1009 12/3/2022 10:00:58 AM 1 1009 12/22/2022 9:08:07 AM 1 1030 12/10/2022 10:02:24 AM 1 1054 12/20/2022 7:06:49 PM 1 1062 12/13/2022 6:48:24 PM 1 1062 12/20/2022 6:34:13 PM 1 1062 12/27/2022 6:42:14 PM 1 1063 12/20/2022 6:07:50 PM 1 1072 12/1/2022 9:35:36 AM 1 1072 12/29/2022 9:25:14 AM 1 1072 12/8/2022 9:46:34 AM 1 1072 12/15/2022 9:15:57 AM 1 1076 12/13/2022 7:12:36 PM 1 1080 12/8/2022 9:53:38 AM 1 1080 12/15/2022 10:22:33 AM 1 1101 12/17/2022 11:09:46 AM 1 1104 12/29/2022 10:05:08 AM 1 1118 12/6/2022 6:23:46 PM 1 1118 12/27/2022 6:24:47 PM 1 1120 12/8/2022 9:48:44 AM 1 1122 12/27/2022 6:20:43 PM 1 1127 12/20/2022 6:03:54 PM 1 1133 12/3/2022 10:28:25 AM 1 1162 12/1/2022 9:38:11 AM 1 1162 12/22/2022 9:55:37 AM 1 1186 12/13/2022 6:08:01 PM 1 1194 12/10/2022 10:22:16 AM 1 1196 12/3/2022 10:34:54 AM 1 1196 12/17/2022 10:53:45 AM 1 1198 12/3/2022 10:26:51 AM 1 1198 12/10/2022 11:16:57 AM 1 1200 12/13/2022 6:22:56 PM 1 1200 12/20/2022 5:56:03 PM 1 1200 12/8/2022 10:15:03 AM 1 1200 12/27/2022 6:04:12 PM 1 1201 12/1/2022 9:04:38 AM 1 1201 12/8/2022 9:16:30 AM 1 1201 12/29/2022 9:22:20 AM 1 1204 12/10/2022 9:27:25 AM 1 1204 12/10/2022 10:20:16 AM 1 1209 12/13/2022 6:19:02 PM 1 1251 12/1/2022 10:01:19 AM 1 1251 12/15/2022 9:57:26 AM 1 1251 12/22/2022 10:57:06 AM 1 1276 12/6/2022 6:17:34 PM 1 1276 12/20/2022 6:20:18 PM 1 1289 12/29/2022 10:32:43 AM 1 1296 12/10/2022 10:23:03 AM 1 1311 12/17/2022 10:04:04 AM 1 1321 12/3/2022 9:59:41 AM 1 1321 12/22/2022 9:11:25 AM 1 1326 12/1/2022 9:01:37 AM 1 1326 12/29/2022 9:06:28 AM 1 1332 12/8/2022 9:06:51 AM 1 1353 12/13/2022 7:04:01 PM 1 1359 12/17/2022 10:26:19 AM 1 1359 12/29/2022 9:36:08 AM 1 1359 12/13/2022 6:05:25 PM 1 1367 12/16/2022 9:40:16 AM 1 1373 12/3/2022 10:47:30 AM 1 1373 12/17/2022 10:37:53 AM 1 1385 12/8/2022 10:08:13 AM 1 1392 12/13/2022 6:29:54 PM 1 1397 12/3/2022 10:58:32 AM 1 1397 12/8/2022 10:21:43 AM 1 1397 12/15/2022 10:09:09 AM 1 1397 12/22/2022 10:01:35 AM 1 1413 12/16/2022 7:43:00 AM 1 1414 12/16/2022 7:40:00 AM 1 1420 12/16/2022 7:41:27 AM 1 1423 12/13/2022 6:45:12 PM 1 1423 12/6/2022 6:12:24 PM 1 1424 12/22/2022 11:18:12 AM 1 1428 12/27/2022 6:11:43 PM 1 1435 12/16/2022 7:42:17 AM 1 1449 12/15/2022 9:42:53 AM 1 1471 12/16/2022 10:28:09 AM 1 1481 12/22/2022 9:59:46 AM 1 1483 12/27/2022 7:00:44 PM 1 1483 12/3/2022 11:55:52 AM 1 1483 12/20/2022 7:18:03 PM 1 1488 12/27/2022 7:20:39 PM 1 1488 12/10/2022 11:07:45 AM 1 1488 12/6/2022 6:43:15 PM 1 1488 12/10/2022 10:19:06 AM 1 1499 12/3/2022 9:37:55 AM 1 1499 12/10/2022 9:37:26 AM 1 1516 12/20/2022 6:29:42 PM 1 1519 12/6/2022 6:09:00 PM 1 1519 12/17/2022 12:24:28 PM 1 1524 12/8/2022 9:20:16 AM 1 1524 12/29/2022 9:17:13 AM 1 1529 12/16/2022 7:38:31 AM 1 1530 12/27/2022 6:28:41 PM 1 1530 12/13/2022 6:01:27 PM 1 1531 12/8/2022 9:39:08 AM 1 1531 12/22/2022 9:37:17 AM 1 1534 12/13/2022 6:24:37 PM 1 1534 12/20/2022 5:51:49 PM 1 1537 12/16/2022 10:24:41 AM 1 1538 12/16/2022 10:24:02 AM 1 1553 12/16/2022 10:23:29 AM 1 1554 12/29/2022 10:48:54 AM 1 1554 12/10/2022 12:21:47 PM 1 1554 12/17/2022 9:32:08 AM 1 1558 12/17/2022 10:34:10 AM 1 1559 12/20/2022 6:27:41 PM 1 1561 12/16/2022 10:20:07 AM 1 1567 12/22/2022 10:24:17 AM 1 1576 12/13/2022 7:09:16 PM 1 1584 12/13/2022 7:01:33 PM 1 1584 12/20/2022 6:35:29 PM 1 1584 12/27/2022 6:45:36 PM 1 1593 12/3/2022 10:06:02 AM 1 1593 12/10/2022 10:05:42 AM 1 1593 12/17/2022 10:07:29 AM 1 1598 12/16/2022 9:34:54 AM 1 1611 12/8/2022 1:30:16 PM 1 1612 12/8/2022 1:27:16 PM 1 1613 12/1/2022 9:23:55 AM 1 1613 12/29/2022 9:53:32 AM 1 1618 12/10/2022 11:11:01 AM 1 1619 12/16/2022 10:30:30 AM 1 1625 12/6/2022 6:38:10 PM 1 1625 12/27/2022 6:50:21 PM 1 1633 12/8/2022 1:27:57 PM 1 1637 12/8/2022 1:28:37 PM 1 1638 12/8/2022 1:29:10 PM 1 1641 12/8/2022 1:24:25 PM 1 1646 12/13/2022 7:22:20 PM 1 1655 12/8/2022 1:31:50 PM 1 1661 12/8/2022 1:23:54 PM 1 1668 12/8/2022 1:29:49 PM 1 1671 12/3/2022 11:04:43 AM 1 1671 12/22/2022 9:38:45 AM 1 1684 12/10/2022 10:41:26 AM 1 1684 12/15/2022 9:37:52 AM 1 1684 12/29/2022 9:33:23 AM 1 1689 12/8/2022 1:22:11 PM 1 1694 12/16/2022 10:10:23 AM 1 1699 12/8/2022 1:25:31 PM 1 1706 12/20/2022 6:41:13 PM 1 1714 12/22/2022 9:24:11 AM 1 1718 12/1/2022 9:17:10 AM 1 1718 12/15/2022 10:04:55 AM 1 1719 12/3/2022 10:02:31 AM 1 1719 12/17/2022 10:47:28 AM 1 1720 12/8/2022 1:26:38 PM 1 1725 12/29/2022 10:54:16 AM 1 1727 12/15/2022 9:22:11 AM 1 1735 12/20/2022 7:05:19 PM 1 1739 12/15/2022 9:06:32 AM 1 1740 12/15/2022 9:08:33 AM 1 1742 12/15/2022 9:23:43 AM 1 1743 12/15/2022 9:28:52 AM 1 1747 12/16/2022 7:40:42 AM 1 1749 12/22/2022 9:05:20 AM 1 1749 12/29/2022 9:05:20 AM 1 1761 12/16/2022 8:02:34 AM 1 1761 12/16/2022 10:28:36 AM 1 1764 12/3/2022 11:13:42 AM 1 1764 12/29/2022 10:42:48 AM 1 1765 12/6/2022 6:14:43 PM 1 1767 12/20/2022 6:49:50 PM 1 1771 12/22/2022 9:30:47 AM 1 1772 12/29/2022 9:08:29 AM 1 1772 12/10/2022 10:37:44 AM 1 1774 12/8/2022 9:32:27 AM 1 1775 12/8/2022 1:26:05 PM 1 1776 12/17/2022 10:31:33 AM 1 1777 12/1/2022 10:04:36 AM 1 1777 12/22/2022 11:02:51 AM 1 1778 12/15/2022 9:47:14 AM 1 1781 12/10/2022 11:20:37 AM 1 1782 12/10/2022 10:04:20 AM 1 1782 12/17/2022 10:19:12 AM 1 1785 12/17/2022 10:01:42 AM 1 1787 12/3/2022 10:46:42 AM 1 1787 12/17/2022 10:42:17 AM 1 1790 12/20/2022 6:53:36 PM 1 1791 12/10/2022 10:45:40 AM 1 1792 12/3/2022 11:20:00 AM 1 1792 12/29/2022 9:57:50 AM 1 1793 12/6/2022 6:59:44 PM 1 1794 12/13/2022 6:33:41 PM 1 1794 12/20/2022 5:49:11 PM 1 1794 12/27/2022 6:03:09 PM 1 1795 12/16/2022 10:30:11 AM 1 1796 12/29/2022 9:43:50 AM 1