Select quier problem

[Nigel Cross]

Can any body tell me why. When this select querie finds duplicate between the [CallLog].cCID and the [Client Addresses], CallLog
It need to puts an "Unknown" record in as well. Could you let me know where I have gone wrong, with this formula.

SELECT CallLog.cCID, CallLog.dDATE, CallLog.tTIME, IIf([cCID]=[Home phone No],[Pefix] & " " & [Forname] & " " & [Surname],IIf([cCID]=[Work phone No],[Pefix] & " " & [Forname] & " " & [Surname],IIf([cCID]=[Mobile phone No],[Pefix] & " " & [Forname] & " " & [Surname],"Unknown"))) AS Name, Last(IIf([Name]="Unknown","",[Client ID No])) AS [Client ID]
FROM [Client Addresses], CallLog
GROUP BY CallLog.cCID, CallLog.dDATE, CallLog.tTIME, IIf([cCID]=[Home phone No],[Pefix] & " " & [Forname] & " " & [Surname],IIf([cCID]=[Work phone No],[Pefix] & " " & [Forname] & " " & [Surname],IIf([cCID]=[Mobile phone No],[Pefix] & " " & [Forname] & " " & [Surname],"Unknown"))), CallLog.[Call ID]
HAVING (((CallLog.dDATE) Between Now() And Now()-7))
ORDER BY CallLog.dDATE DESC , CallLog.[Call ID] DESC;

Thanks Nigel Cross

 

[Galaxiom]

Rather than the complex IIf statements, create the query with a join between CallLog and [Client Addresses] on cCID and the phone number field. It would be much faster.

However you would need to join CallLog to three copies of [Client Addresses].
I would highly recommend you change the structure of the Phone number records in currently in [Client Addresses].
Move the phone numbers to another table related to the [Client Addresses] by ClientID. Include another field to indicate the phone number type. (Use a foreign key from a PhoneType table.)

Then your query will be dead simple.