By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
443,730 Members | 1,559 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 443,730 IT Pros & Developers. It's quick & easy.

access query

P: n/a
When I place the following in my query

ProductivityHours:
IIf([dbo_tblservice_activity]![activity_sysid]=85,actualtimeintounittime([units_activity_num]),IIf([dbo_tblservice_activity].[activity_sysid]=231,3,Sum(IIf([dbo_tblservice_activity]![activity_sysid]=33,[dbo_tblservice_activity]![units_activity_num]/60,IIf([dbo_tblservice_activity]![activity_sysid]=102
Or [dbo_tblservice_activity]![activity_sysid]=158 Or
[dbo_tblservice_activity]![activity_sysid]=219,0,IIf([dbo_tblservice_activity]![units_activity_num]>12
And
[dbo_tblservice_activity]![units_activity_num]<38,0.5,IIf([dbo_tblservice_activity]![units_activity_num]<63,1,IIf([dbo_tblservice_activity]![units_activity_num]<93,1.5,IIf([dbo_tblservice_activity]![units_activity_num]<123,2,IIf([dbo_tblservice_activity]![units_activity_num]<153,2.5,IIf([dbo_tblservice_activity]![units_activity_num]<181,3,0)))))))))))

I get an error that indicates that this needs to be aggregated so when
I group by it says that I can not have a sum in an aggregate. When I
replace actualtimeintounittime([units_activity_num]), first line
toward, end with a number the query runs just fine. This would be ok
but I need to have a dynamic calculation in this place instead of a
static number. Any wisdom would be appreciated. Might have to create
sql server query and create a crystal report for this but really do not
have the time to do that. Thanks.

Mar 13 '06 #1
Share this Question
Share on Google+
1 Reply


P: n/a
Tablenames prefixed by "dbo_" are normal in SQL Server, but not in Access.
If your query is, in fact, an Access query, I'd suggest putting this logic
in a user-defined-function, so it can be written in a more user-friendly,
indented form. I don't write, nor do I try to analyze others'
multiply-nested IIFs, when there is any reasonable way to avoid it.

Larry Linson
Microsoft Access MVP

<wa***@fcswichita.org> wrote in message
news:11*********************@u72g2000cwu.googlegro ups.com...
When I place the following in my query

ProductivityHours:
IIf([dbo_tblservice_activity]![activity_sysid]=85,actualtimeintounittime([units_activity_num]),IIf([dbo_tblservice_activity].[activity_sysid]=231,3,Sum(IIf([dbo_tblservice_activity]![activity_sysid]=33,[dbo_tblservice_activity]![units_activity_num]/60,IIf([dbo_tblservice_activity]![activity_sysid]=102
Or [dbo_tblservice_activity]![activity_sysid]=158 Or
[dbo_tblservice_activity]![activity_sysid]=219,0,IIf([dbo_tblservice_activity]![units_activity_num]>12
And
[dbo_tblservice_activity]![units_activity_num]<38,0.5,IIf([dbo_tblservice_activity]![units_activity_num]<63,1,IIf([dbo_tblservice_activity]![units_activity_num]<93,1.5,IIf([dbo_tblservice_activity]![units_activity_num]<123,2,IIf([dbo_tblservice_activity]![units_activity_num]<153,2.5,IIf([dbo_tblservice_activity]![units_activity_num]<181,3,0)))))))))))

I get an error that indicates that this needs to be aggregated so when
I group by it says that I can not have a sum in an aggregate. When I
replace actualtimeintounittime([units_activity_num]), first line
toward, end with a number the query runs just fine. This would be ok
but I need to have a dynamic calculation in this place instead of a
static number. Any wisdom would be appreciated. Might have to create
sql server query and create a crystal report for this but really do not
have the time to do that. Thanks.

Mar 14 '06 #2

This discussion thread is closed

Replies have been disabled for this discussion.