How to write query to extract date only for different customers on the same day.

[privateguy]

I want to select the dates where two or more different customers were shown properties on the same day. I want to exclude the dates where only a singular customer was shown properties on that day. For example:

ShowDate    Customer    Property
7/21/2017    James    123 Main
7/18/2017    James    231 Park
7/18/2017    James    345 Forest
7/18/2017    James    678 Central
6/19/2017    Carmen    531 Charleston
6/19/2017    James    898 Broadway
6/19/2017    James    543 Appleton

Only the date 6/19/2017 should be returned because properties where shown to both Carmen and James on the same day. Any help would certainly be appreciated.

[gnawoncents]

Welcome to Bytes, privateguy!
Your answer will depend on how you are trying to do this (VBA or Query/SQL). Regardless, you will have to do a two-step process that first finds all unique records, combining ShowDate and Customer. Next, based on those results, count how many are for any given day (anything over 1 means that at least two different people where shown properties on that day). Below is some SQL you can plug into two queries, if you want the results displayed via Query. Otherwise, just modify them to VBA and you can use the same idea to get results in a recordset, etc.

First Query (qryProperties) SQL:

SELECT DISTINCT tbProperties.ShowDate, tbProperties.Customer
FROM tbProperties;
 

Second Query SQL:

SELECT DISTINCT qryProperties.ShowDate
FROM qryProperties
WHERE (((DCount("ShowDate","qryProperties","ShowDate = #" & [ShowDate] & "#"))>1));
 

Disclaimer: This may not be the most elegant way to do this, so feel free to jump in if anyone sees a better one.

[twinnyfo]

privateguy,

I'm not sure this is any more elegant, but it does accomplish the task in one Query instead of two:

SELECT tblProperties.ShowDate
FROM tblProperties
WHERE Not DCount("[Customer]","tblProperties","[ShowDate] = #" & [ShowDate] & "# AND [Customer] <> '" & [Customer] & "'") = 0
GROUP BY tblProperties.ShowDate;

Hope this hepps!

[privateguy]

Thank you both for your replies. I am using VB. I had the same logic in mind but did not know of an easy way to write the query. In both of your examples it seems that I would have to know the date to insert it into the code. My workaround was to loop through the distinctive show dates and then test each individual date to see if the customer count was greater than one. If so, then I placed the date in a a combo box. Below is the code that I used. If you know of a simpler way, please advise. Again, thank you for taking out the time to respond.

Dim dbs As Database
Dim rst1 As Recordset
Dim rst2 As Recordset                                        
Dim qry1 As String
Dim qry2 As String
Dim DatesFromQuery As String                                        
Dim i As Integer
 
Set dbs = CurrentDb
DatesFromQuery = ""
 
 
qry1 = "Select Distinct ShowDates from qryShowings "
Set rst1 = dbs.OpenRecordset(qry1)
 
    If rst1.RecordCount = 0 Then
 
        MsgBox "no dates available"
    Else                                        
        rst1.MoveLast
        rst1.MoveFirst                                                    
        DistinctDates = rst1.RecordCount 
 
        For i = 1 To rst1.RecordCount - 1
 
            qry2 = "Select Customer from qryShowings where ShowDate =" & "#" & rst1("ShowDate") & "#" & "group by Customer"
 
            Set rst2 = dbs.OpenRecordset(qry2)
            rst2.MoveLast
            rst2.MoveFirst
 
            If rst2.RecordCount > 1 Then
 
                If DatesFromQuery = "" Then
                    DatesFromQuery = rst1("ShowDate")
                Else
                    DatesFromQuery = DatesFromQuery & "; " & rst1("ShowDate")
                End If
 
            End If
 
            rst2.Close
            rst1.MoveNext
 
        Next i
        MsgBox "Final:  " & DatesFromQuery
 
    End If
 
    'Put results in combobox
    Me.ReportDates.RowSource = DatesFromQuery

[twinnyfo]

privateguy,

First, please use Code Tags when posting any code.

Second, if you look closely at both gnawoncents and my replies, it is not necessary to know the date. We are using the field name [ShowDate] (which you have provided as a Field Name) as either a field criteria or within our grouping criteria.

I believe both examples would work.

[privateguy]

Twinnyfo,

This is my first time in seeking assistance on a forum. To be honest, I don't know what you mean by using Code Tags when pasting code. I will try the examples provided. Thank you again.

[twinnyfo]

Code tags are codes you place around your code so that it formats properly. These are found in a button on the text entry display "[CODE/]. These tags allow your code to look like this:

This is my code with code tags

instead of this:

This is my code without code tags

[Frinavale]

I may be a bit off here since I don't usually work with Access but you should be able to Group By your ShowDate and select distinct ShowDate/Customer(s) having a count that is greater than or equal to 2.

Something like:

SELECT ShowDate, Count(*) as NumCustomersShown FROM (SELECT DISTINCT ShowDate, Customer FROM @PropertyShowings)
Group By ShowDate
Having Count(*)>=2

Where my @PropertyShowings is a temporary table:

declare @PropertyShowings table(ShowDate date, Customer nvarchar(50), Property nvarchar(255));
declare @DistinctPropertyShowings table(ShowDate date, Customer nvarchar(50));
insert into @PropertyShowings Values('7/21/2017', 'James','123 Main Street');
insert into @PropertyShowings Values('7/18/2017', 'James','231 Park Road');
insert into @PropertyShowings Values('7/18/2017', 'James','345 Forest Street');
insert into @PropertyShowings Values('7/18/2017', 'James','678 Central Ave');
insert into @PropertyShowings Values('6/19/2017', 'Carmen','531 Charleston Ave');
insert into @PropertyShowings Values('6/19/2017', 'James','898 Broadway Road');
insert into @PropertyShowings Values('6/19/2017', 'James','543 Appleton Lane');

If you were to do this in TSQL (MS Sql Server) you could have:

Select ShowDate, count(distinct Customer) as NumCustomersShown From @PropertyShowings 
Group By ShowDate
Having Count(distinct Customer)>=2;

But apparently Access doesn't support Count(distinct ...) so you have to select the distinct values first and then do a count on what was selected.

For more information see:

• The Having Clause
• The Group By Clause
• ALL, DISTINCT, DISTINCTROW, TOP Predicates
• SQL Distinct (where I learned about Access's lack of support for COUNT(DISTINCT column_name))