Results 1 to 12 of 12
  1. #1
    dccjr3927 is offline Competent Performer
    Windows 10 Access 2016
    Join Date
    Jan 2019
    Posts
    130

    SELECT DISTINCT with COUNT

    I have a field that has 8 possible values (selected from a combo box). I am trying to create an SQL statement that returns a count of each of these items between 2 dates.




    Code:
    "SELECT COUNT (DISTINCT Process) FROM WOTracking WHERE Date_Time >= #" & Format(dt1, "yyyy-mm-dd") & "# AND Date_Time < #" & Format(dt2, "yyyy-mm-dd") & "#;"
    I am getting a Missing Operator Error. Any help would be greatly appreciated.

  2. #2
    June7's Avatar
    June7 is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,815
    Field name is Process?

    "SELECT Process, Count("*") AS CountProc FROM WOTracking WHERE Date_Time BETWEEN #" & Format(dt1, "yyyy-mm-dd") & "# AND #" & Format(dt2, "yyyy-mm-dd") & "# GROUP BY Process;"

    Use Access query designer to help get correct syntax then structure for VBA.
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  3. #3
    dccjr3927 is offline Competent Performer
    Windows 10 Access 2016
    Join Date
    Jan 2019
    Posts
    130
    Thanks!!!! As always.

  4. #4
    dccjr3927 is offline Competent Performer
    Windows 10 Access 2016
    Join Date
    Jan 2019
    Posts
    130
    I am getting a Type Mismatch. Date_Time is Date/Time in table, dt1 and dt2 as Date, Process is Short Text in table. I don't understand what could be mismatched.

  5. #5
    June7's Avatar
    June7 is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,815
    Sorry, no quotes around the *: Count(*)
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  6. #6
    dccjr3927 is offline Competent Performer
    Windows 10 Access 2016
    Join Date
    Jan 2019
    Posts
    130
    Good Morning. I am sure that I am doing something wrong here, or I have not been clear about what I am trying to accomplish. I realize that I have omitted something that might be very important. I am trying to use this SQL statement as the filter on a report. The complete sub is:

    Code:
    Private Sub cmdGo1_Click() ' Contract Report - Invoicing
    Dim CountProc As Integer
    Dim strRptSQL1 As String
    Dim strRptSQL2 As String
    dt1 = Me.tbxDate1
    dt2 = Me.tbxDate2
    
    
    strRptSQL1 = "SELECT * FROM WOTracking WHERE ContractCo = '" & Me.cbxContractCo & "' AND  Date_Time >= #" & Format(dt1, "yyyy-mm-dd") & "# AND Date_Time < #" & Format(dt2, "yyyy-mm-dd") & "#;"
    DoCmd.OpenReport "ContInvoice", acViewPreview, strRptSQL1
    strRptSQL2 = "SELECT DISTINCT Process, Count(*) AS CountProc FROM WOTracking WHERE Date_Time BETWEEN #" & Format(dt1, "yyyy-mm-dd") & "# AND #" & Format(dt2, "yyyy-mm-dd") & "# GROUP BY Process;"
    DoCmd.OpenReport "ProcessInvoiceSub", acViewPreview, strRptSQL2
    
    
    End Sub
    It is generating both reports. The second one with the Process counts just creating a list of all of the processes between the dates (no counts). So this leads me to 2 questions. How to get each process listed just once with the count next to it? And how to pass the CountProc for each Process to the report?

  7. #7
    dccjr3927 is offline Competent Performer
    Windows 10 Access 2016
    Join Date
    Jan 2019
    Posts
    130
    This was a copy of the above post. Accidentally posted it twice.
    Last edited by dccjr3927; 04-19-2019 at 08:31 AM. Reason: Mistake

  8. #8
    June7's Avatar
    June7 is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,815
    Don't use DISTINCT with GROUP BY.
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  9. #9
    Join Date
    Apr 2017
    Posts
    1,673
    Code:
    strRptSQL2 = "SELECT Process, Count(Process) AS CountProc FROM WOTracking WHERE Date_Time BETWEEN #" & Format(dt1, "yyyy-mm-dd") & "# AND #" & Format(dt2, "yyyy-mm-dd") & "# GROUP BY Process;"
    
    How to get each process listed just once with the count next to it? And how to pass the CountProc for each Process to the report?
    You base the report on query from strRptSQL1, and group report by Process. All process info and count of instances of process goes into group header/footer, all info about instance specific details are in group body. The query from strRptSQL2 is not needed.

  10. #10
    June7's Avatar
    June7 is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,815
    Agree with Arvil. Use report Sorting & Grouping features with a simple SELECT query. Do the aggregate calc in textbox in report header/footer. This allows display of detail data as well as summary calcs. If you don't want details to show, code can hide the Detail section. One report object can serve both output requirements.
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  11. #11
    dccjr3927 is offline Competent Performer
    Windows 10 Access 2016
    Join Date
    Jan 2019
    Posts
    130
    Thanks guys. I will give it a try.

  12. #12
    dccjr3927 is offline Competent Performer
    Windows 10 Access 2016
    Join Date
    Jan 2019
    Posts
    130
    Unfortunately, my directive from on high has changed for this report. I have begun a new thread, as it is much more...complicated now. We will call this one closed, but a great reference. Thanks for all the help.

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

Similar Threads

  1. Replies: 4
    Last Post: 01-15-2019, 11:50 AM
  2. Distinct Count
    By ertweety in forum Queries
    Replies: 1
    Last Post: 04-15-2014, 10:10 AM
  3. Count Distinct
    By Newbie11 in forum Reports
    Replies: 8
    Last Post: 01-09-2013, 12:47 PM
  4. Replies: 8
    Last Post: 04-21-2011, 05:29 PM
  5. Count Distinct in Access
    By georgerudy in forum Access
    Replies: 1
    Last Post: 11-28-2010, 01:24 PM

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