Query to Run on Count, Distinct and Where

[Tezcatlipoca]

Take a look a the attached database, which is a vastly cut-down version of the project I'm currently working on.

I have a form, frmReports. On it, I have a listbox, AdvisorList (containing a list of all those names that appear under UserName in the tblEmployees); two date fields, txtStartDate and txtEndDate; and an unbound textbox, Results.

Currently, I can easily use DCount functions in unbound text boxes to tot up the total records according to the dates. for example, in the proper project, selecting an advisor shows the total number of calls taken by that advisor in an unbound textbox that contains the folloiwng ControlSource code:

=DCount("MemberNumber","tblLogs","[UserName] = '" & [AdvisorList] & "' AND [CallLog] BETWEEN #" & Format([Forms]![frmReports]![txtStartDate],"mm/dd/yy") & "# AND #" & Format([Forms]![frmReports]![txtEndDate],"mm/dd/yy") & "#")

What I now want to do is implement the ability for a user to select any advisor they like, and any dates they like, and have the textbox Results return the number of individual members helped by that advisor between the dates given.

For example, in the table. Advisor 'Don Quixote' has logged two calls for member 057426 (on the 8th and 11th of November) and two for member 029544 (on the 11th and the 15th). This is four calls in total, but it only relates to 2 distinct members.

So I'm trying to do is create a query that will check the UserName from tblLogs against AdvisorList, the CallLog against the txtStartDate and txtEndDate, ignore the records in tblLogs that have a blank UserName, and, if all criteria match, will return the number of distinct MemberNumber records.

So, from the 1st of November to the 20th of November, Don Quixote has helped two distinct members. However, if I reset the dates to 12th and 20th of November, he has helped only one distinct member (albeit with 2 calls).

 

[jal]

I got started on this but ran out of time.

Private Sub btnCalc_Click()
Dim sql As String
Dim cr As String
cr = vbCrLf
sql = "SELECT COUNT( MemberNumber) AS Result" & cr _
& "FROM tblLogs" & cr _
& "WHERE [UserName] Like '" & Forms!frmReports!AdvisorList & "'" & cr _
& "AND CallLog BETWEEN #" & Format(Forms![frmReports]![txtStartDate], "mm/dd/yy") & "#" & cr _
& "AND #" & Format([Forms]![frmReports]![txtEndDate], "mm/dd/yy") & "#"
MsgBox Scalar(sql)
End Sub


Public Function Scalar(ByVal query As String) As Variant
Dim rs As New ADODB.Recordset
rs.Open query, CurrentProject.Connection, adOpenStatic, adLockReadOnly
If rs.RecordCount > 1 Then
Call Err.Raise(500, , "Your custom Execute Scalar method has returned more than one value.")
ElseIf rs.RecordCount = 0 Then
Scalar = Null
Else: rs.MoveFirst
Scalar = rs("Result")
End If
rs.Close
Set rs = Nothing
End Function

 

[Tezcatlipoca]

An interesting possible solution, jal. Unfortunately, I add your code into my VB form code (although I'm amending the btnCalc_Click to an AdvisorList_AfterUpdate() event as I want the code to fire when a user clicks a listbox selection, not a button; I'm also amending the MsgBox Scalar(sql) to Me.Results = Scalar(sql) as I want the results in the unbound textbox Results, not a messagebox). However, with or without my small amendments, it doesn't appear to filter properly.

My unbound text box, Results, still only - albeit correctly - reports the total number of records logged for that advisor, not the total number of distinct members who have that advisor's name logged against them. So, to use my original example from the table, it shows that Don Quixote helped 4 members, which is not correct. He took four calls, yes, but he helped only 2 members!

 

[jal]

The msgbox and button are just for testing purposes - I'm just trying to get you started. Try this version of the query.

Dim sql As String
Dim cr As String
cr = vbCrLf
sql = "SELECT Count(MemberNumber) as Result FROM " & cr _
& "( " & cr _
& "SELECT DISTINCT MemberNumber " & cr _
& "FROM tblLogs" & cr _
& "WHERE [UserName] Like '" & Forms!frmReports!AdvisorList & "'" & cr _
& "AND CallLog BETWEEN #" & Format(Forms![frmReports]![txtStartDate], "mm/dd/yy") & "#" & cr _
& "AND #" & Format([Forms]![frmReports]![txtEndDate], "mm/dd/yy") & "#" _
& ")" & cr
MsgBox Scalar(sql)

 

[Tezcatlipoca]

Perfect, thanks jal. I can see how your code works, and have now made a few small amendments to suit the setup of my form proper. Thanks again