Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

Query to Determine Most Recent Phone Number Used

Posted on 2016-07-20
20
Medium Priority
?
51 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 23

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 53

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
Survive A High-Traffic Event with Percona

Your application or website rely on your database to deliver information about products and services to your customers. You can’t afford to have your database lose performance, lose availability or become unresponsive – even for just a few minutes.

 
LVL 53

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 2000 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

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

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

Access developers frequently have requirements to interact with Excel (import from or output to) in their applications.  You might be able to accomplish this with the TransferSpreadsheet and OutputTo methods, but in this series of articles I will di…
The Windows Phone Theme Colours is a tight, powerful, and well balanced palette. This tiny Access application makes it a snap to select and pick a value. And it doubles as an intro to implementing WithEvents, one of Access' hidden gems.
Get people started with the utilization of class modules. Class modules can be a powerful tool in Microsoft Access. They allow you to create self-contained objects that encapsulate functionality. They can easily hide the complexity of a process from…
Do you want to know how to make a graph with Microsoft Access? First, create a query with the data for the chart. Then make a blank form and add a chart control. This video also shows how to change what data is displayed on the graph as well as form…

670 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