Access Subquery Max Date/Time field

[ruci1225]

The query itself works fine and is built with the following logic.

Qry1: qrycall log and Qry2: Speedtolead is linked by phone number with the relationship Speedtolead has all results and is joined to calllog with results that only match phonenumber from Speedtolead

The query works fine

SELECT [2_SpeedtoLead_April_Phone Number Format].LeadDate, [2_SpeedtoLead_April_Phone Number Format].SyStudentID, [2_SpeedtoLead_April_Phone Number Format].StudentName, [2_SpeedtoLead_April_Phone Number Format].Phone, [2_SpeedtoLead_April_Phone Number Format].WorkPhone, [2_SpeedtoLead_April_Phone Number Format].otherphone, [2_SpeedtoLead_April_Phone Number Format].mobilenumber, [2_SpeedtoLead_April_Phone Number Format].StatusCode, [2_SpeedtoLead_April_Phone Number Format].StartDate, [2_SpeedtoLead_April_Phone Number Format].AdmRep, [2_SpeedtoLead_April_Phone Number Format].StatusDesc, [2_SpeedtoLead_April_Phone Number Format].LeadSrcDesc, [2_SpeedtoLead_April_Phone Number Format].AgencyDesc, [2_SpeedtoLead_April_Phone Number Format].RepTeam, [1_qryCallLog_AttemptorContact_Phonefmt].CallDirection, [1_qryCallLog_AttemptorContact_Phonefmt].LocalUserId, [1_qryCallLog_AttemptorContact_Phonefmt].PhoneNumberFinal, [1_qryCallLog_AttemptorContact_Phonefmt].InitiatedDate, [1_qryCallLog_AttemptorContact_Phonefmt].ConnectedDate, [1_qryCallLog_AttemptorContact_Phonefmt].CallDurationSeconds, [1_qryCallLog_AttemptorContact_Phonefmt].HoldDurationSeconds


FROM [2_SpeedtoLead_April_Phone Number Format] LEFT JOIN 1_qryCallLog_AttemptorContact_Phonefmt ON [2_SpeedtoLead_April_Phone Number Format].PhoneFinal = [1_qryCallLog_AttemptorContact_Phonefmt].PhoneNumberFinal;


The only issue is that in the qrycalllog their are multiple items for the same phone number as their are different contacts time/date in the InitiatedDate field.
I am trying to make it so that InitiatedDate field only brings back the latest(max) result for that phone number.

I first tried doing a MAX in the intiateddate fiend in the query design but it did not work as I got an agregate error.
I then tried creating a subquery which worked with no errors but had zero results.
The SQL is as follows:
(See the end for the added subquery)


SELECT [2_SpeedtoLead_April_Phone Number Format].LeadDate, [2_SpeedtoLead_April_Phone Number Format].SyStudentID, [2_SpeedtoLead_April_Phone Number Format].StudentName, [2_SpeedtoLead_April_Phone Number Format].Phone, [2_SpeedtoLead_April_Phone Number Format].WorkPhone, [2_SpeedtoLead_April_Phone Number Format].otherphone, [2_SpeedtoLead_April_Phone Number Format].mobilenumber, [2_SpeedtoLead_April_Phone Number Format].StatusCode, [2_SpeedtoLead_April_Phone Number Format].StartDate, [2_SpeedtoLead_April_Phone Number Format].AdmRep, [2_SpeedtoLead_April_Phone Number Format].StatusDesc, [2_SpeedtoLead_April_Phone Number Format].LeadSrcDesc, [2_SpeedtoLead_April_Phone Number Format].AgencyDesc, [2_SpeedtoLead_April_Phone Number Format].RepTeam, [1_qryCallLog_AttemptorContact_Phonefmt].CallDirection, [1_qryCallLog_AttemptorContact_Phonefmt].LocalUserId, [1_qryCallLog_AttemptorContact_Phonefmt].PhoneNumberFinal, [1_qryCallLog_AttemptorContact_Phonefmt].InitiatedDate, [1_qryCallLog_AttemptorContact_Phonefmt].ConnectedDate, [1_qryCallLog_AttemptorContact_Phonefmt].CallDurationSeconds, [1_qryCallLog_AttemptorContact_Phonefmt].HoldDurationSeconds
FROM [2_SpeedtoLead_April_Phone Number Format] LEFT JOIN 1_qryCallLog_AttemptorContact_Phonefmt ON [2_SpeedtoLead_April_Phone Number Format].PhoneFinal = [1_qryCallLog_AttemptorContact_Phonefmt].PhoneNumberFinal
WHERE [1_qryCallLog_AttemptorContact_Phonefmt].InitiatedDate =
(SELECT MAX([1_qryCallLog_AttemptorContact_Phonefmt].InitiatedDate)
FROM [1_qryCallLog_AttemptorContact_Phonefmt]);



Please help

[jzwp11]

I think that you need to create a Max query as follows:

SELECT PhoneNumberFinal, Max(InitiatedDate) as MostRecentContact
FROM calllogtable
GROUP BY PhoneNumberFinal

Save the above query.

Now create another query that joins the above query back to the calllogtable and join by both the PhoneNumberFinal fields and the date fields (InitiatedDate=MostRecentContact). Select the fields you want and then save this query.

Now you can join (LEFT JOIN as you describe in your post) the above query with your [2_SpeedtoLead_April_Phone Number Format] table/query.