471,079 Members | 1,015 Online
Bytes | Software Development & Data Engineering Community
Post +

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 471,079 software developers and data experts.

Please explain how IIF Function works (was Access Help)

Can anyone tell me wht this expression does in ACCESS

IIf([Count]>1,([SumOfQty]-[Max])/([Count]-1),[Average]) AS ExcludeOutlierAvg

I Encountered this in a query

I do not Understand [COUNT], [Average] mean

Also in
[SumOfQty], SumOfQty is a field in the table I am trying to access.

Thanx in advance
Sep 1 '07 #1
2 2031
missinglinq
3,532 Expert 2GB
[Count], [SumOfQty], [Max] and [Average] are all field names.

The general syntax is IIf(expression, truepart, falsepart)
If the expression evaluates to True then you do/use the truepart
If the expression evaluates to Flase then you do/use the falsepart

IIf([Count]>1 means
If Count is greater than 1 then
[SumOfQty]-[Max]) / ([Count]-1) AS ExcludeOutlierAvg

If Count is equal to or less than 1 then
[Average] AS ExcludeOutlierAvg

Please remember to provide a meaningful Title for any threads you start! This helps to ensure that other members, and also the general public, will have a better chance of finding answers to any similar questions. It is difficult for the experts to answer questions when there is not enough details provided to understand the problem.

Please take the time to read the Posting Guidelines, paying particular attention to Give a clear title to your question.

Linq ;0)>
Sep 1 '07 #2
I am sorry abt the title .... but actually my q is more on database terms

my query is

Expand|Select|Wrap|Line Numbers
  1. SELECT BulkActivity_Max_Min_SubQuery_01.OrderDetail.Item AS Item, Count(BulkActivity_Max_Min_SubQuery_01.SumOfQty) AS [Count], Sum(BulkActivity_Max_Min_SubQuery_01.SumOfQty) AS SumOfQty, Max(BulkActivity_Max_Min_SubQuery_01.SumOfQty) AS [Max], Min(BulkActivity_Max_Min_SubQuery_01.SumOfQty) AS [Min], Avg(BulkActivity_Max_Min_SubQuery_01.SumOfQty) AS Average, Import_table_for_Planner.units_sold_per_day
  2. FROM BulkActivity_Max_Min_SubQuery_01 INNER JOIN Import_table_for_Planner ON BulkActivity_Max_Min_SubQuery_01.Item = Import_table_for_Planner.Item
  3. GROUP BY BulkActivity_Max_Min_SubQuery_01.OrderDetail.Item, Import_table_for_Planner.units_sold_per_day;
  4.  
check the part

Max(BulkActivity_Max_Min_SubQuery_01.SumOfQty) AS [Max]

is this like a array thing???

because there is a group by.

Could u explain this plz
Sep 1 '07 #3

Post your reply

Sign in to post your reply or Sign up for a free account.

Similar topics

9 posts views Thread by hope | last post: by
reply views Thread by Kermitus | last post: by
5 posts views Thread by =?Utf-8?B?TWljaGFlbA==?= | last post: by

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.