Query issue

[JRJohnson4]

I have built a query called "Carotid Reports - Symptomatic (Totals) 1" that gives a Sum of the following fields:

CS
CS Carotid Symptomatic
CE
CE Carotid Symptomatic
30 Days Post Procedure Stroke
30 Days Post Procedure Death

In addition, this table only calculates the totals for these fields that meet the Status of "Emergent".

The problem that I am having is that I need the query to only count those cases that fall under the 30 Days Post Procedure Stroke or 30 Days Post procedure Death, if they meet the following criteria:

[P4P / Goals]![CS Carotid Symptomatic]=True Or [P4P / Goals]![CE Carotid Symptomatic]=True

I have tried utilizing an IIF function that included the Sum function if the expression above was true, but it didn't work. Here is the expression that I utilized:

IIf([P4P / Goals]![CS Carotid Symptomatic]=True Or [P4P / Goals]![CE Carotid Symptomatic]=True,Sum(IIf([P4P / Goals]![30 day post procd stroke]=True,1,0)),0)

I have also tried utilizing separate IIF statements in the Criteria field on the query, but that just asked for information, instead of, perform the calculation like I wanted.

The problem is that I have two separate reports: 1) Symptomatic 2) Asymptomatic
And I need these two 30 Days Post procedure fields to calculate the total cases that fall under Carotid Symptomatic or Carotid Asymptomatic, instead of, calculating the total for both types of cases.

Any assistance would be greatly appreciated.

 

[plog]

IIf([P4P / Goals]![CS Carotid Symptomatic]=True Or [P4P / Goals]![CE Carotid Symptomatic]=True,Sum(IIf([P4P / Goals]![30 day post procd stroke]=True,1,0)),0)

Didn't really follow your description, but did find something wrong with your code. The IIf needs to be inside the SUM, not vice versa:

SUM(IIF({criteria here}, {true value here}, {false value here}))

 

[JRJohnson4]

plog,

If I wanted to determine the Sum of 30 Days Post Procedure Stroke, then I know that the expression would be:

Sum(IIF([P4P / Goals]![30 Days Post Procedure Strokes]=True,1,0))

But the problem that I am having is that I need the Sum of 30 Days Post procedure Strokes based upon the additional criteria of

[P4P / Goals]![CS Carotid Symptomatic]=True OR [P4P / Goals]![CE Carotid Symptomatic]=True

The reason being is that when it comes to Carotid procedures they can be either Symptomatic or Asymptomatic. And I need accurate totals based upon whether the Carotid Stent (CS) or Carotid Endartectomy (CE) procedure fell underneath the Symptomatic or Asymptomatic criteria, instead of, just finding a total for all of them together. Hope this better explains the issue that I am having and what I am trying to accomplish.

 

[plog]

Did you try what I suggested?

Perhaps if I restated it:

SUM needs to be the first function in your statement. Put as many IIfs inside as you want, but SUM needs to be the first function listed.

 

[JRJohnson4]

I am actually working with your suggestion now and trying to see if I can get the right totals based on the data That I ran a number of calculations on for a specific date range.

 

[plog]

You can also logically reduce to just one IIF:

IIF((A OR B) AND C, true, false)

Still need the SUM on the outside, but makes statement a little easier to follow.

 

[JRJohnson4]

plog,

Thank you for all of your help. I did what you suggested and it worked like a champ. I figured that it was probably something pretty simple that I was overlooking. Thanks again for your help and have a great day. I am off to start working on the next database project that I need to finish working on.