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.
1  1316 
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. This thread has been closed and replies have been disabled. Please start a new discussion. Similar topics
by: Frnak McKenney |
last post by:
Back when computer dinosaurs roamed the earth and the precursors to
today's Internet were tiny flocks of TDMs living symbiotically with
the silicon giants, tracking access to data processing...
|
by: Joris Kempen |
last post by:
Hi people,
I know that the question has come around sometimes:
How to open an Access Report using ASP and export it to for example
RTF.
I'm trying to implement the first method of David...
|
by: Sean C. |
last post by:
Helpful folks,
Most of my previous experience with DB2 was on s390 mainframe systems
and the optimizer on this platform always seemed very predictable and
consistent. Since moving to a WinNT/UDB...
|
by: bhbgroup |
last post by:
I have a query on one large table. I only add one condition, i.e. a
date (the SQL reads like 'where date > parameterdate'.
This query is rather quick if 'parameterdate' is either explicitly...
|
by: Neil |
last post by:
We are running an Access 2000 MDB with a SQL 7 back end. Our network guy is
upgrading to Windows Server 2003 and wants to upgrade Office and SQL Server
at the same time. We're moving to SQL Server...
|
by: tt40 |
last post by:
Anyone know how to prevent Access 2002 from automatically breaking all
the incorrect joins in a query and then automatically saving the broken
query?
This is what I would call stupid design...
|
by: InnoCreate |
last post by:
Hi everyone.
I've recently written a classic asp website which uses an MS Access
datasource. I know this is less than an ideal data source as it has
limited functionality. I have a search form on...
|
by: mnjkahn via AccessMonster.com |
last post by:
I'm running Access 2003, modifying a query that has over 45 fields. When I
right click on the field name in Query Design View, and then click Build,
Access crashes before the Build window...
|
by: jsacrey |
last post by:
Hey everybody, got a secnario for ya that I need a bit of help with.
Access 97 using linked tables from an SQL Server 2000 machine.
I've created a simple query using two tables joined by one...
|
by: magickarle |
last post by:
Hi, I got a pass-through query (that takes about 15 mins to process)
I would like to integrate variables to it.
IE: something simple:
Select EmplID from empl_Lst
where empl_lst.timestamp between...
|
by: isladogs |
last post by:
The next Access Europe User Group meeting will be on Wednesday 3 Apr 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM).
In this session, we are pleased to welcome former...
|
by: ryjfgjl |
last post by:
In our work, we often need to import Excel data into databases (such as MySQL, SQL Server, Oracle) for data analysis and processing. Usually, we use database tools like Navicat or the Excel import...
|
by: taylorcarr |
last post by:
A Canon printer is a smart device known for being advanced, efficient, and reliable. It is designed for home, office, and hybrid workspace use and can also be used for a variety of purposes. However,...
|
by: aa123db |
last post by:
Variable and constants
Use var or let for variables and const fror constants.
Var foo ='bar';
Let foo ='bar';const baz ='bar';
Functions
function $name$ ($parameters$) {
}
...
|
by: ryjfgjl |
last post by:
In our work, we often receive Excel tables with data in the same format. If we want to analyze these data, it can be difficult to analyze them because the data is spread across multiple Excel files...
|
by: emmanuelkatto |
last post by:
Hi All, I am Emmanuel katto from Uganda. I want to ask what challenges you've faced while migrating a website to cloud.
Please let me know.
Thanks!
Emmanuel
|
by: BarryA |
last post by:
What are the essential steps and strategies outlined in the Data Structures and Algorithms (DSA) roadmap for aspiring data scientists? How can individuals effectively utilize this roadmap to progress...
|
by: nemocccc |
last post by:
hello, everyone, I want to develop a software for my android phone for daily needs, any suggestions?
|
by: Sonnysonu |
last post by:
This is the data of csv file
1 2 3
1 2 3
1 2 3
1 2 3
2 3
2 3
3
the lengths should be different i have to store the data by column-wise with in the specific length.
suppose the i have to...
| |
