IIF statement query problem - please help...

[neil_val]

Hi - Please help!!!!

I have created 3 queries to get to the point that I am at but still I
can't get it to work to my advantage.

qryTest

EmployeeName TotalHrs PMonth
group by Sum group by

This works fine

qryChartTest

EmployeeName SumOfTotalHrs PMonth ContractualHrs: [Week]*40
TargetHrs: [Weeks]*50
Count Sum group by
group by group by

I need to determine if the SumOfTotalHrs is greater than 40 hrs per
week but less than 50 hrs per week then count the hours after 40. I
also need another column to determin that if the SumOfTotalHrs is
greater than 50 hrs per week then count the hrs after 50.

This is need for a rpt chart which should be grouped by months and
show as a SumOfSumOfTotalHrs, TargetHrs, SumOf40+Hours and
SumOf50+Hours.

Is this possible?? I have been working on this for 2 full days and
have searched high and low for a solution - PLEASE HELP!!

 

[Guest]

EmployeeName SumOfTotalHrs PMonth ContractualHrs: [Week]*40
TargetHrs: [Weeks]*50 Count Sum group by
group by group by
A couple of questions - What is the datatype and sample data in ---
PMonth, [Week], & [Weeks]
In the above I think you need Expression instead of Group By for the last two.