Solved

Query to Determine Most Recent Phone Number Used

Posted on 2016-07-20
20
43 Views
Last Modified: 2016-07-24
I have a requirement to retrieve the most recent phone number used in contacting each property.

The fields in table ‘tblProperty_CallResults’ which is a log of all call results

ID                   autonumber, primary key
PropertyID      Number, many calls may be logged for the same Property
PhoneNum      Number, Long
CallResultID      Number, Long
DateOfCall      Date/Time, format short date
TImeOfCall      Date/Time, format short time
CallerTypeID      Number, Long

The result set will contain only two fields
PropertyID
PhoneNum

The result set will only contain 1 record for each propertyID.  The PhoneNum on that record will be the PhoneNum from the most recent call.  The most recent call is the one with the most recent ‘TimeOFCall’ on the most recent ‘DateOfCall’.

Other factors

In creating the result set:

Only Include records where the ‘CallResultID’ = 4 or 5
Only Include records where the ‘CallerTypeID= 1 Or 2 Or 9 Or 8 Or 6 Or 16

I'm having a mental block on this one.
0
Comment
Question by:mlcktmguy
  • 8
  • 7
  • 2
  • +3
20 Comments
 
LVL 22

Expert Comment

by:Ferruccio Accalai
ID: 41721153
Try this
SELECT tblProperty_CallResults.PropertyID, tblProperty_CallResults.PhoneNum
FROM tblProperty_CallResults INNER JOIN tblProperty_CallResults AS tblProperty_CallResults_1 ON tblProperty_CallResults.ID = tblProperty_CallResults_1.ID
WHERE (((tblProperty_CallResults.CallResultID)=4 Or (tblProperty_CallResults.CallResultID)=5) AND ((tblProperty_CallResults.CallerTypeID) In (1,2,6,8,9,16)))
GROUP BY tblProperty_CallResults.PropertyID, tblProperty_CallResults.PhoneNum, tblProperty_CallResults.CallResultID, tblProperty_CallResults.CallerTypeID

Open in new window

0
 
LVL 48

Expert Comment

by:Rgonzo1971
ID: 41721171
Hi,

pls try

SELECT tblProperty_CallResults.PropertyId, First(tblProperty_CallResults.PhoneNum) AS FirstOfPhoneNum
FROM tblProperty_CallResults
WHERE (((tblProperty_CallResults.CallResultID) In (4,5)) AND ((tblProperty_CallResults.CallerTypeID) In (1,2,6,8,9,16)))
GROUP BY tblProperty_CallResults.PropertyId
ORDER BY Max(tblProperty_CallResults.[TimeOfCall]) DESC , Max(tblProperty_CallResults.DateOfCall) DESC;

Open in new window

Regards
0
 
LVL 1

Author Comment

by:mlcktmguy
ID: 41721290
Thanks for the ideas.
Ferruccio Accalai:  The query you suggested return multiple lines for each PropertyID

Rgonzo1971:  The query you suggested returns one line for each propertyID. However, when I checked the results against the phone log I found Properties where the most recent call was not being returned.

For example:  PropertyID 2902907         Your query Returned Phone Number  2153292302

Based On what I see below  it should have returned    phone number 2155946976

Here are all the log records for that propertyID

Results For Property
Can you see the issue?
0
 
LVL 48

Expert Comment

by:Rgonzo1971
ID: 41721337
then try

SELECT tblProperty_CallResults.PropertyId, First(tblProperty_CallResults.PhoneNum) AS FirstOfPhoneNum
FROM tblProperty_CallResults
WHERE (((tblProperty_CallResults.CallResultID) In (4,5)) AND ((tblProperty_CallResults.CallerTypeID) In (1,2,6,8,9,16)))
GROUP BY tblProperty_CallResults.PropertyId
ORDER BY Max(tblProperty_CallResults.DateOfCall]) DESC , Max(tblProperty_CallResults.TimeOfCall) DESC;

Open in new window

0
 
LVL 119

Expert Comment

by:Rey Obrero
ID: 41721484
try this query


SELECT T.PropertyId, T.PhoneNum
FROM tblProperty_CallResults As T
inner join
(Select T2.PropertyId, T2.PhoneNum, max(t2.TimeOfCall) as maxTimeOfCall,max(t2.DateOfCall) as maxDateOfCall
 FROM tblProperty_CallResults As T2
 GROUP BY T2.PropertyId, T2.PhoneNum) As T3
On T.PropertyId=T3.PropertyId and T.PhoneNum= T3.PhoneNum and t.TimeOfCall=T3.maxTimeOfCall and t.DateOfCall=T3.maxDateOfCall
Having T.CallResultID In (4,5) AND T.CallerTypeID In (1,2,6,8,9,16)
0
 
LVL 1

Author Comment

by:mlcktmguy
ID: 41721530
Rgonzo1971:  Thanks but the result is the same

Rey:  I get and Error 'Having Clause Without Grouping Or Aggregation on
Having T.CallResultID In (4,5) AND T.CallerTypeID In (1,2,6,8,9,16)
0
 
LVL 119

Expert Comment

by:Rey Obrero
ID: 41721545
try this

SELECT T.PropertyId, T.PhoneNum
 FROM tblProperty_CallResults As T
 inner join
 (Select T2.PropertyId, T2.PhoneNum, max(t2.TimeOfCall) as maxTimeOfCall,max(t2.DateOfCall) as maxDateOfCall
  FROM tblProperty_CallResults As T2
  GROUP BY T2.PropertyId, T2.PhoneNum) As T3
 On T.PropertyId=T3.PropertyId and T.PhoneNum= T3.PhoneNum and t.TimeOfCall=T3.maxTimeOfCall and t.DateOfCall=T3.maxDateOfCall
Where T.CallResultID In (4,5) AND T.CallerTypeID In (1,2,6,8,9,16)
0
 
LVL 1

Expert Comment

by:pep1993
ID: 41721559
How about this.  It assumes that DateOfCall and TimeOfCall are not null.  In testing, it will return only 1 phone number per property (unless two calls have exactly the same date/time values and meet the result and type criteria, which should be rare).

SELECT tblProperty_CallResults.PropertyID, tblProperty_CallResults.PhoneNum
FROM tblProperty_CallResults
WHERE (((DateValue([tblProperty_CallResults.DateOfCall])+TimeValue([tblProperty_CallResults.TimeOfCall])) In (SELECT TOP 1 Max(DateValue([CallResults_Max.DateOfCall])+TimeValue([CallResults_Max.TimeOfCall])) AS DateTimeOfCall
FROM tblProperty_CallResults AS CallResults_Max
WHERE (((CallResults_Max.PropertyID)=[tblProperty_CallResults].[PropertyID]) AND ((CallResults_Max.CallResultID) In (4,5)) AND ((CallResults_Max.CallerTypeID) In (1,2,9,8,6,16)));)) AND ((tblProperty_CallResults.CallResultID) In (4,5)) AND ((tblProperty_CallResults.CallerTypeID) In (1,2,9,8,6,16)));
0
 
LVL 1

Author Comment

by:mlcktmguy
ID: 41722076
Rey Obrero:  Your revised query returns multiple lines for some PropertyID's

pep1993: Your query ran for several minutes with no result set shown, I cancelled it.  Not sure if works but it would have to be more efficient.
0
 
LVL 119

Expert Comment

by:Rey Obrero
ID: 41722081
can you upload a copy of the table?
0
Free Trending Threat Insights Every Day

Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

 
LVL 31

Expert Comment

by:Helen_Feddema
ID: 41724735
One problem might be that you have two Date fields -- one formatted to show the date and the other to show the time.  You only need one field, since Access does not have separate Date and Time fields.  Use the Format property in a control to display the date, time, or both, as needed.  I would dump one of these fields, so there is only one Date field to examine for the most recent call.
0
 
LVL 119

Expert Comment

by:Rey Obrero
ID: 41724749
try this revised query


SELECT T.PropertyId, T.PhoneNum
 FROM tblProperty_CallResults As T
 inner join
 (Select T2.PropertyId, T2.PhoneNum, max([t2].[DateOfCall] + [t2].[TimeOfCall]) as maxDateOfCall
  FROM tblProperty_CallResults As T2
  GROUP BY T2.PropertyId, T2.PhoneNum) As T3
 On T.PropertyId=T3.PropertyId and T.PhoneNum= T3.PhoneNum and ([t].[DateOfCall] + [t].[TimeOfCall])=T3.maxDateOfCall
Where T.CallResultID In (4,5) AND T.CallerTypeID In (1,2,6,8,9,16)
0
 
LVL 1

Author Comment

by:mlcktmguy
ID: 41725482
Helen_Feddema:  I cannot combine the fields, they are user entered.  Date of Call default to the current date but can be overridden.  Typically the user only enters the time of call.

Rey:  Thanks again for your suggestion but the new query returns multiple lines for some propertyID's also.  I'd really have to clean/scrub a lot of data from the table to upload it.  I won't have time to do that in the near future.
0
 
LVL 119

Expert Comment

by:Rey Obrero
ID: 41725491
does the returned result propertyID have different phone numbers?

export the result to excel and upload the excel file.

or create a select query against the table selecting only the relevant non sensitive information from the table then export the result to excel and upload the excel file
0
 
LVL 119

Expert Comment

by:Rey Obrero
ID: 41725496
try this

SELECT T.PropertyId, T.PhoneNum
  FROM tblProperty_CallResults As T
  inner join
  (Select T2.PropertyId, T2.PhoneNum, max([t2].[DateOfCall] + [t2].[TimeOfCall]) as maxDateOfCall
   FROM tblProperty_CallResults As T2
   GROUP BY T2.PropertyId, T2.PhoneNum
   Having T.CallResultID In (4,5) AND T.CallerTypeID In (1,2,6,8,9,16)
   ) As T3
  On T.PropertyId=T3.PropertyId and T.PhoneNum= T3.PhoneNum and ([t].[DateOfCall] + [t].[TimeOfCall])=T3.maxDateOfCall
0
 
LVL 1

Author Comment

by:mlcktmguy
ID: 41725516
Thanks for trying to help Rey

In the prior Query:
<<does the returned result propertyID have different phone numbers?>>
Yes, the cases I looked at had the propertyID multiple times and each propertyID had a different phone number.


In the new query:
I get a 'Enter Parameter Value' popup for T.CallResultID and T.CallerTypeID
0
 
LVL 119

Expert Comment

by:Rey Obrero
ID: 41725525
<Yes, the cases I looked at had the propertyID multiple times and each propertyID had a different phone number.>

OK, try this query

SELECT T.PropertyId, T.PhoneNum
  FROM tblProperty_CallResults As T
  inner join
  (Select T2.PropertyId, max([t2].[DateOfCall] + [t2].[TimeOfCall]) as maxDateOfCall
   FROM tblProperty_CallResults As T2
   GROUP BY T2.PropertyId) As T3
  On T.PropertyId=T3.PropertyId and ([t].[DateOfCall] + [t].[TimeOfCall])=T3.maxDateOfCall
 Where T.CallResultID In (4,5) AND T.CallerTypeID In (1,2,6,8,9,16)
0
 
LVL 1

Author Comment

by:mlcktmguy
ID: 41726758
Thank you Rey, that one is much better but there are duplicates.

All of the duplicate that I checked were the same situation:  the same phone call had been logged multiple times.

Example:
EE_DupPhoneEntry
0
 
LVL 119

Accepted Solution

by:
Rey Obrero earned 500 total points
ID: 41726809
ok, try this


SELECT DISTINCT T.PropertyId, T.PhoneNum
   FROM tblProperty_CallResults As T
   inner join
   (Select T2.PropertyId, max([t2].[DateOfCall] + [t2].[TimeOfCall]) as maxDateOfCall
    FROM tblProperty_CallResults As T2
    GROUP BY T2.PropertyId) As T3
   On T.PropertyId=T3.PropertyId and ([t].[DateOfCall] + [t].[TimeOfCall])=T3.maxDateOfCall
  Where T.CallResultID In (4,5) AND T.CallerTypeID In (1,2,6,8,9,16)
0
 
LVL 1

Author Closing Comment

by:mlcktmguy
ID: 41726946
Thanks for sticking with it Rey.  No dups in that one.
Much appreciated
0

Featured Post

Why You Should Analyze Threat Actor TTPs

After years of analyzing threat actor behavior, it’s become clear that at any given time there are specific tactics, techniques, and procedures (TTPs) that are particularly prevalent. By analyzing and understanding these TTPs, you can dramatically enhance your security program.

Join & Write a Comment

Suggested Solutions

Experts-Exchange is a great place to come for help with solutions for your database issues, and many problems are resolved within minutes of being posted.  Others take a little more time and effort and often providing a sample database is very helpf…
'Between' is such a common word we rarely think about it but in SQL it has a very specific definition we should be aware of. While most database vendors will have their own unique phrases to describe it (see references at end) the concept in common …
As developers, we are not limited to the functions provided by the VBA language. In addition, we can call the functions that are part of the Windows operating system. These functions are part of the Windows API (Application Programming Interface). U…
What’s inside an Access Desktop Database. Will look at the basic interface, Navigation Pane (Database Container), Tables, Queries, Forms, Report, Macro’s, and VBA code.

708 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

12 Experts available now in Live!

Get 1:1 Help Now