IIF function does not work take a look.... 1

[Pizarro]

IIf([Units Cume].Flight = "FLT2",.001 * SUM([Units Cume].TotAlloc), 0) with an alias as GrossFlt2

The function is to identify the record by its FLT number, in this case FLT2 and then perform a calculation if FLT2 does not match then value is 0 (zero)

I am linked via an Ms Access ADP to an MSDE SQL database.

I tried the COALESCE function COALESCE (.001 * SUM(dbo.[Units Cume].TotAlloc), 0) which works fine but I don't know where to specify the FLT number.

The end result would be various colums with aliases FLT1, FLT2 etc with the appropriate calculations.

Anyone want to take a stab a this - I would be grateful

PS I am upsizing to SQL from Acces because my data is to big now. I have produce fabulous apps in MS Access and would like to keep it as a front end

Regards - Julio

 

[cmmrfrds]

IIf([Units Cume].Flight = "FLT2",.001 * SUM([Units Cume].TotAlloc), 0) with an alias as GrossFlt2

Look up the CASE statement, it is quite flexible.

Case [Units Cume].Flight
when 'FLT2' then
.001 * SUM([Units Cume].TotAlloc)
else 0
end as GrossFlt2

OR

Case
when [Units Cume].Flight = 'FLT2' then
.001 * SUM([Units Cume].TotAlloc)
else 0
end as GrossFlt2

 

[Pizarro]

Thanks for your reply...
I not sure how or where do I use this ...
Does it go in a function(query)? If so I have tried with no success or do I set it up in a module as a function and then do I call it from a function(query)

Regards

 

[cmmrfrds]

This is sql server Transact_SQL syntax, which would be used in a pass-thru query. In the query grid this would be SQL coded directly in a pass-thru query.

Select afield, anotherfield,
Case [Units Cume].Flight
when 'FLT2' then
.001 * SUM([Units Cume].TotAlloc)
else 0
end as GrossFlt2,
another2field,
etc
From yourtable

If this is not what you want then I did not understand the question.

 

[Pizarro]

This is what it looks like in the query pane: I have tried your suggestion and put it in above the FROM statement but and error messages comes back, could it be because the query is grouping?

SELECT dbo.[Orders Cume].FeedDate, dbo.[Orders Cume].OrderNo, dbo.[Orders Cume].Sponsor, dbo.[Orders Cume].OrderDpt
FROM dbo.[Orders Cume] INNER JOIN
dbo.[Units Cume] ON dbo.[Orders Cume].OrderNo = dbo.[Units Cume].OrderNo AND dbo.[Orders Cume].FeedDate = dbo.[Units Cume].FeedDate
GROUP BY dbo.[Orders Cume].FeedDate, dbo.[Orders Cume].OrderNo, dbo.[Orders Cume].Sponsor, dbo.[Orders Cume].OrderDpt
HAVING (dbo.[Orders Cume].OrderNo = N'031813')

 

[cmmrfrds]

Yes, any field in an aggregate query ( i.e. group by) must be an aggregate or in the group by clause. An aggregate is min, max, count, etc.....


I think you will need some variation like this.
Sum( case when [Units Cume].Flight = 'FLT2' then [Units Cume].TotAlloc * .001 else 0) as GrossFlt

You probably need to do the calc first then the SUM.

 

[Pizarro]

Heres what I came up with ...And all thanks to you!
SELECT [Orders Cume].FeedDate, [Orders Cume].OrderNo, [Orders Cume].Sponsor,
[Orders Cume].OrderDpt,
0.001*Sum(case [Units Cume].Flight when 'FLT2' then [Units Cume].TotAlloc else 0 end) AS GrossFlt2,
0.001*Sum(case [Units Cume].Flight when 'FLT3' then [Units Cume].TotAlloc else 0 end) AS GrossFlt3,
0.001*Sum(case [Units Cume].Flight when 'FLT4' then [Units Cume].TotAlloc else 0 end) AS GrossFlt4,
0.001*Sum(case [Units Cume].Flight when 'FLT5' then [Units Cume].TotAlloc else 0 end) AS GrossFlt5,
-- 0.001*Sum(case when [Units Cume].Flight='FLT2' Or [Units Cume].Flight='FLT3' Or [Units Cume].Flight='FLT4' Or [Units Cume].Flight='FLT5' then 0 else [Units Cume].TotAlloc end) AS GrossFltOth,
0.001*Sum(case [Units Cume].Flight when 'FLT1' then [Units Cume].TotAlloc else 0 end) AS GrossFltOth,
0.001*Sum([Units Cume].TotAlloc) AS GrossTot,
Max(case [Units Cume].Flight when 'FLT2' then [Units Cume].FlightCPM else 0 end) AS CPMFlt2,
Max(case [Units Cume].Flight when 'FLT3' then [Units Cume].FlightCPM else 0 end) AS CPMFlt3,
Max(case [Units Cume].Flight when 'FLT4' then [Units Cume].FlightCPM else 0 end) AS CPMFlt4,
Max(case [Units Cume].Flight when 'FLT5' then [Units Cume].FlightCPM else 0 end) AS CPMFlt5
FROM [Orders Cume] INNER JOIN [Units Cume]
ON ([Orders Cume].OrderNo = [Units Cume].OrderNo) AND ([Orders Cume].FeedDate = [Units Cume].FeedDate)
WHERE [Orders Cume].OrderNo=@order_number
GROUP BY [Orders Cume].FeedDate, [Orders Cume].OrderNo,
[Orders Cume].Sponsor, [Orders Cume].OrderDpt;
It worked great!
Thanks again!

 

[cmmrfrds]

You are welcome. I am glad you could take the little example I gave and make it work for you. Good job.