Solved

Query to Determine Most Recent Phone Number Used

Posted on 2016-07-20
20
48 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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 51

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
Space-Age Communications Transitions to DevOps

ViaSat, a global provider of satellite and wireless communications, securely connects businesses, governments, and organizations to the Internet. Learn how ViaSat’s Network Solutions Engineer, drove the transition from a traditional network support to a DevOps-centric model.

 
LVL 51

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 120

Expert Comment

by:Rey Obrero (Capricorn1)
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 120

Expert Comment

by:Rey Obrero (Capricorn1)
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 120

Expert Comment

by:Rey Obrero (Capricorn1)
ID: 41722081
can you upload a copy of the table?
0
 
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 120

Expert Comment

by:Rey Obrero (Capricorn1)
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 120

Expert Comment

by:Rey Obrero (Capricorn1)
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 120

Expert Comment

by:Rey Obrero (Capricorn1)
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 120

Expert Comment

by:Rey Obrero (Capricorn1)
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 120

Accepted Solution

by:
Rey Obrero (Capricorn1) 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

How our DevOps Teams Maximize Uptime

Our Dev teams are like yours. They’re continually cranking out code for new features/bugs fixes, testing, deploying, responding to production monitoring events and more. It’s complex. So, we thought you’d like to see what’s working for us. Read the use case whitepaper.

Question has a verified solution.

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

Composite queries are used to retrieve the results from joining multiple queries after applying any filters. UNION, INTERSECT, MINUS, and UNION ALL are some of the operators used to get certain desired results.​
As tax season makes its return, so does the increase in cyber crime and tax refund phishing that comes with it
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.
With Microsoft Access, learn how to start a database in different ways and produce different start-up actions allowing you to use a single database to perform multiple tasks. Specify a start-up form through options: Specify an Autoexec macro: Us…

739 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