Using IIF function without aggregates. . . Possible?

[Rmck87]

i want to use the IIF function without the aggregate in my query, so i can accomplish this. I want to get the sum of b.hourly_rate*e.hours_actual if pm.pm_billing_method_id <> &quot;F&quot; . But, if pm.pm_billing_method_id = &quot;F&quot; then i want to just bring the straight value of pm.billing_amount. Is there a way to do this?

Select Sum(IIF(pm.pm_billing_method_id = &quot;F&quot;, pm.billing_amount, (b.hourly_rate*e.hours_actual))) AS [Revenue]


This is what i was doing, but it was summing up pm.billing_amount. i want the straight value of pm.billing_amount not the sum. Any help?

Thanks a lot.

-Ryan

One Ring to Rule Them All, One Ring to Find Them, One Ring to Bring Them All, and in the Darkness Bind Them.

 

[markgrizzle]

Is this what you wanted?

Select IIF(pm.pm_billing_method_id = &quot;F&quot;, pm.billing_amount, Sum((b.hourly_rate*e.hours_actual))) AS [Revenue]

 

[Rmck87]

Well, that is what i was trying to accomplish, but any time that i ran the code using this, an error would come up saying that this code:

'IIF(pm.pm_billing_method_id = &quot;F&quot;, pm.billing_amount, Sum((b.hourly_rate*e.hours_actual))) AS [Revenue] '

needs to be in the group by clause, then when i put it in the group by clause, it says that the aggregate 'Sum' cannot be in the group by clause.

Thanks for all the help.

One Ring to Rule Them All, One Ring to Find Them, One Ring to Bring Them All, and in the Darkness Bind Them.

 

[cmmrfrds]

Easiest to use 2 queries, one to sum the amount and another to list. A couple of ways to do this are; one, make a sum query and then join that query to the query that will list the results, or combine both queries together with the sum query being a derived query. In Access it is most common to make a separate sum query.

query1
select field1, sum(field) as sfield from table.

query2
select A.field1, B.sfield from table as A
Inner Join query1 as B on A.field1 = B.field1

 

[Rmck87]

I just want to keep it on one query. I managed to get it working. Thanks for the help tho. What i did was i put this in the Select statement:'


IIF(pm.pm_billing_method_id = &quot;F&quot;, max(pm.billing_amount), Sum((b.hourly_rate*e.hours_actual))) AS [Revenue]


' Then i put this in the Group By Clause'


pm.pm_billing_method_id'


Thanks for the help!

One Ring to Rule Them All, One Ring to Find Them, One Ring to Bring Them All, and in the Darkness Bind Them.