Solved

Query to Determine Most Recent Phone Number Used

Posted on 2016-07-20
20
44 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 49

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 49

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
Backup Your Microsoft Windows Server®

Backup all your Microsoft Windows Server – on-premises, in remote locations, in private and hybrid clouds. Your entire Windows Server will be backed up in one easy step with patented, block-level disk imaging. We achieve RTOs (recovery time objectives) as low as 15 seconds.

 
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

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

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…
I'm trying, I really am. But I've seen so many wrong approaches involving date(time) boundaries I despair about my inability to explain it. I've seen quite a few recently that define a non-leap year as 364 days, or 366 days and the list goes on. …
Show developers how to use a criteria form to limit the data that appears on an Access report. It is a common requirement that users can specify the criteria for a report at runtime. The easiest way to accomplish this is using a criteria form that a…
In Microsoft Access, learn different ways of passing a string value within a string argument. Also learn what a “Type Mis-match” error is about.

920 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

13 Experts available now in Live!

Get 1:1 Help Now