Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 58
  • Last Modified:

Query to Determine Most Recent Phone Number Used

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
mlcktmguy
Asked:
mlcktmguy
  • 8
  • 7
  • 2
  • +3
1 Solution
 
Ferruccio AccalaiSenior developer, analyst and customer assistance Commented:
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
 
Rgonzo1971Commented:
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
 
mlcktmguyAuthor Commented:
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
Industry Leaders: 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!

 
Rgonzo1971Commented:
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
 
Rey Obrero (Capricorn1)Commented:
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
 
mlcktmguyAuthor Commented:
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
 
Rey Obrero (Capricorn1)Commented:
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
 
pep1993Commented:
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
 
mlcktmguyAuthor Commented:
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
 
Rey Obrero (Capricorn1)Commented:
can you upload a copy of the table?
0
 
Helen FeddemaCommented:
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
 
Rey Obrero (Capricorn1)Commented:
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
 
mlcktmguyAuthor Commented:
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
 
Rey Obrero (Capricorn1)Commented:
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
 
Rey Obrero (Capricorn1)Commented:
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
 
mlcktmguyAuthor Commented:
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
 
Rey Obrero (Capricorn1)Commented:
<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
 
mlcktmguyAuthor Commented:
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
 
Rey Obrero (Capricorn1)Commented:
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
 
mlcktmguyAuthor Commented:
Thanks for sticking with it Rey.  No dups in that one.
Much appreciated
0

Featured Post

Get expert help—faster!

Need expert help—fast? Use the Help Bell for personalized assistance getting answers to your important questions.

  • 8
  • 7
  • 2
  • +3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now