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