IIF Statement help

[Slab_Rankle]

Hey all,

I'm having an issue with a query I'm trying to create. The purpose of the query is to ultimately create a 'performance' report so management can see how well a particular employee is doing. We have two types of employee's, filterers and advisors. A filterer finds leads for the advisor who then generates a sale. I currently have a query that shows how many leads a filterer has input (CountOfFilterer) and how many leads an advisor has taken (CountOfAdvisor). Now, this works fine but there's another thing an advisor can do which is self generate a lead. This occurs when both the filterer and advisor contain the same name.

What I want to do is create a query that counts the amount of times a filterer has inputted a lead, the amount of times an advisor has taken a lead, and the amount of times an advisor gets a self generated lead. I have the following fields in my query:

Filterer
Advisor
LeadSource

Now, this is my current SQL code for my query (which contains the CountOfFilterer and CountOfAdvisor):

SELECT [Basic Details].Filterer, [Basic Details].Advisor, [Basic Details].LeadSource, Count([Basic Details].Filterer) AS CountOfFilterer, Count([Basic Details].Advisor) AS CountOfAdvisor
FROM [Basic Details]
GROUP BY [Basic Details].Filterer, [Basic Details].Advisor, [Basic Details].LeadSource
HAVING ((([Basic Details].Filterer) Like "*" & [Enter a Filterer] & "*") AND (([Basic Details].Advisor) Like "*" & [Enter an Advisor] & "*") AND (([Basic Details].LeadSource) Like "*" & [Enter a Broker] & "*"));


How would I create another expression that checks to see if both the Filterer and Advisor values are the same? I assume I need to do some sort of IF statement but I'm hopeless with them and I'm really struggling to figure this out. If anyone needs
more information from me to help then just say and I'll post what I can!

 

[Guus2005]

you can add a field to your select query:

iif (Filterer=Advisor,1,0) as newfield.

this new field equals 1 if Filterer and Advisor are the same. you can sum this value.

HTH

 

[Slab_Rankle]

That's brilliant Guus2005!! It works! I'm still getting my head around IIF statements, your reply's helped though, at least I can apply this to anything similar in the future . Thanks again!!