By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
435,483 Members | 3,261 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 435,483 IT Pros & Developers. It's quick & easy.

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

P: 12
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:

Expand|Select|Wrap|Line Numbers
  1. ShowDate    Customer    Property
  2. 7/21/2017    James    123 Main
  3. 7/18/2017    James    231 Park
  4. 7/18/2017    James    345 Forest
  5. 7/18/2017    James    678 Central
  6. 6/19/2017    Carmen    531 Charleston
  7. 6/19/2017    James    898 Broadway
  8. 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.
Mar 3 '18 #1
Share this Question
Share on Google+
7 Replies

P: 214
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:
Expand|Select|Wrap|Line Numbers
  1. SELECT DISTINCT tbProperties.ShowDate, tbProperties.Customer
  2. FROM tbProperties;
Second Query SQL:
Expand|Select|Wrap|Line Numbers
  1. SELECT DISTINCT qryProperties.ShowDate
  2. FROM qryProperties
  3. 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.
Mar 5 '18 #2

Expert Mod 2.5K+
P: 3,284

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

Expand|Select|Wrap|Line Numbers
  1. SELECT tblProperties.ShowDate
  2. FROM tblProperties
  3. WHERE Not DCount("[Customer]","tblProperties","[ShowDate] = #" & [ShowDate] & "# AND [Customer] <> '" & [Customer] & "'") = 0
  4. GROUP BY tblProperties.ShowDate;
Hope this hepps!
Mar 5 '18 #3

P: 12
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.

Expand|Select|Wrap|Line Numbers
  1. Dim dbs As Database
  2. Dim rst1 As Recordset
  3. Dim rst2 As Recordset                                        
  4. Dim qry1 As String
  5. Dim qry2 As String
  6. Dim DatesFromQuery As String                                        
  7. Dim i As Integer
  9. Set dbs = CurrentDb
  10. DatesFromQuery = ""
  13. qry1 = "Select Distinct ShowDates from qryShowings "
  14. Set rst1 = dbs.OpenRecordset(qry1)
  16.     If rst1.RecordCount = 0 Then
  18.         MsgBox "no dates available"
  19.     Else                                        
  20.         rst1.MoveLast
  21.         rst1.MoveFirst                                                    
  22.         DistinctDates = rst1.RecordCount 
  24.         For i = 1 To rst1.RecordCount - 1
  26.             qry2 = "Select Customer from qryShowings where ShowDate =" & "#" & rst1("ShowDate") & "#" & "group by Customer"
  28.             Set rst2 = dbs.OpenRecordset(qry2)
  29.             rst2.MoveLast
  30.             rst2.MoveFirst
  32.             If rst2.RecordCount > 1 Then
  34.                 If DatesFromQuery = "" Then
  35.                     DatesFromQuery = rst1("ShowDate")
  36.                 Else
  37.                     DatesFromQuery = DatesFromQuery & "; " & rst1("ShowDate")
  38.                 End If
  40.             End If
  42.             rst2.Close
  43.             rst1.MoveNext
  45.         Next i
  46.         MsgBox "Final:  " & DatesFromQuery
  48.     End If
  50.     'Put results in combobox
  51.     Me.ReportDates.RowSource = DatesFromQuery
Mar 5 '18 #4

Expert Mod 2.5K+
P: 3,284

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.
Mar 5 '18 #5

P: 12

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.
Mar 5 '18 #6

Expert Mod 2.5K+
P: 3,284
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:

Expand|Select|Wrap|Line Numbers
  1. This is my code with code tags
instead of this:

This is my code without code tags
Mar 5 '18 #7

Expert Mod 5K+
P: 9,731
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:
Expand|Select|Wrap|Line Numbers
  1. SELECT ShowDate, Count(*) as NumCustomersShown FROM (SELECT DISTINCT ShowDate, Customer FROM @PropertyShowings)
  2. Group By ShowDate
  3. Having Count(*)>=2
Where my @PropertyShowings is a temporary table:
Expand|Select|Wrap|Line Numbers
  1. declare @PropertyShowings table(ShowDate date, Customer nvarchar(50), Property nvarchar(255));
  2. declare @DistinctPropertyShowings table(ShowDate date, Customer nvarchar(50));
  3. insert into @PropertyShowings Values('7/21/2017', 'James','123 Main Street');
  4. insert into @PropertyShowings Values('7/18/2017', 'James','231 Park Road');
  5. insert into @PropertyShowings Values('7/18/2017', 'James','345 Forest Street');
  6. insert into @PropertyShowings Values('7/18/2017', 'James','678 Central Ave');
  7. insert into @PropertyShowings Values('6/19/2017', 'Carmen','531 Charleston Ave');
  8. insert into @PropertyShowings Values('6/19/2017', 'James','898 Broadway Road');
  9. 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:
Expand|Select|Wrap|Line Numbers
  1. Select ShowDate, count(distinct Customer) as NumCustomersShown From @PropertyShowings 
  2. Group By ShowDate
  3. 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:
Mar 5 '18 #8

Post your reply

Sign in to post your reply or Sign up for a free account.