IIf Question 1

[murphy123]

Hello,
I am working on a report result that gives me a total for the Agent header with the amount of calls answered by an agent. The header section needs to display the amount of necessary calls "Yes" and the amount of not necessary calls "No" as well. I used an IIf function in the control source of my text box =Sum(IIf([CALL NECESSARY]="No",1,0)) and the same =Sum(IIf([CALL NECESSARY]="Yes",1,0)) the result is not correct. Is there a way that I can get a count in the control source of how many "No" and "Yes" each agent has?

My Agent header report output should be:

Agent Total Calls Necessary Not Necessary
Mary 20 15 5

Thanks in advance.

 

[dhookom]

the result is not correct" why not? We can't see the results or your data. Is [Call Necessary] a text field with values of "Yes" and "No"?

Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]

 

[murphy123]

Hi,

[Call Necessary] is a text field with "Yes" and "No" list values.

Thanks

 

[Crowley16]

can't see any syntax errors, maybe the problem is that the base data isn't quite correct.

try using like "*YES*" and like "*NO*" and maybe try putting a ucase in there as well...

--------------------
Procrastinate Now!

 

[murphy123]

Here is a small sample of my data:
Call# Agent Call Necessary
1 Smith Yes
2 Smith No
3 Smith Yes
4 Davis No
5 Davis No
6 Davis Yes
7 Jones Yes
8 Jones Yes

Here is what I need on my report output on the Agent Header:

Yes No
Agent Total Calls Necessary Not Necessary
Smith 3 2 1
Davis 3 1 2
Jone 2 2 0

I hope this helps to explain what I am trying to do.

Thanks.

 

[Crowley16]

you've got groupings in there, I'm assuming this is going in the headder of a report with existing groupings.

you cannot have different sets of groupings in a single report, so best way would be to put a subreport in the agent headder.

--------------------
Procrastinate Now!

 

[dhookom]

mhoegg,
You seemed to have ignored 2/3 of my request

"the result is not correct" why not? We can't see the results or your data.

Can you tell us how your report is currently displaying and what's wrong with it?

Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]

 

[murphy123]

Hi,

Sorry I am just responding back now. I was on vacation and I just came back. I was able to work a little more on the issue I had.

I decided to work in the query itself and I created an expression to count the "No" and "Yes". On the report the AGENT gave me the grouping totals I was looking for.

In my MS Access query design I added:

NotNecessary: Count(IIf([CALL NECESSARY]="No",[AGENT]))

Necessary: Count(IIf([CALL NECESSARY]="Yes",[AGENT]))

The report now gives me the output I was looking for.

Agent Total Calls Necessary NotNecessary
Smith 3 2 1
Davis 3 1 2
Jone 2 2 0

Thank you for all of your ideas and suggestions.