Results 1 to 3 of 3
  1. #1
    spmiyamoto is offline Novice
    Windows 10 Office 365
    Join Date
    Jan 2023
    Posts
    2

    Basic Query Question-Counting Records with a common field value

    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

  2. #2
    Micron is offline Virtually Inert Person
    Windows 10 Access 2016
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    12,737
    Remove DateOfEvent from query as it is creating a group for every individual date-time value.
    The more we hear silence, the more we begin to think about our value in this universe.
    Paraphrase of Professor Brian Cox.

  3. #3
    Gicu's Avatar
    Gicu is online now VIP
    Windows 10 Access 2010 32bit
    Join Date
    Jul 2015
    Location
    Kelowna, BC, Canada
    Posts
    4,101
    If you need to order the records by the visit date you can use either FirstVisitDate:Min(DateOfVisit) or LastVisitDate:Max(DateOfVisit) and order your query on those fields:

    SELECT DISTINCT ClientVisits.ClientNumber, Min(ClientVisits.DateofVisit) AS FirstVisitDate, Count(ClientVisits.ClientNumber) AS CountOfClientNumber
    FROM ClientVisits
    GROUP BY ClientVisits.ClientNumber
    HAVING (((ClientVisits.DateofVisit)>=#12/1/2022# And (ClientVisits.DateofVisit)<=#12/31/2022#) AND ((Count(ClientVisits.ClientNumber))>0))
    ORDER BY FirstVisitDate;

    Cheers,
    Vlad Cucinschi
    MS Access Developer
    http://forestbyte.com/

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Counting Common Attributes
    By Brandon12 in forum Queries
    Replies: 4
    Last Post: 09-28-2017, 09:38 AM
  2. Counting records in report from a select field
    By LaughingBull in forum Access
    Replies: 5
    Last Post: 08-23-2015, 12:09 PM
  3. Replies: 4
    Last Post: 07-18-2013, 03:14 AM
  4. Basic Query Sort Question
    By Siiig in forum Queries
    Replies: 4
    Last Post: 01-14-2013, 10:02 AM
  5. Basic query design question
    By megabrown in forum Queries
    Replies: 1
    Last Post: 12-15-2010, 09:10 AM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums