calculate Sum/Total of unbound field

[jackryan]

hello, i am facing problem with my unbound field Food1 calculation, i can get my Food1 to calculate, but when I created another unbound field Sum_Food1 to SUm/TOtal my Food1, and i run my report, its been asking me for the Parameter Value of Sum_Food1


Food1= IIf([Text78] Between [Forms]![Salary Calculation]![Text0] And [Forms]![Salary Calculation]![Text2],[Food],IIf([SumOfNormal]<>Max([SumOfNormal]),IIf([Food]>0,[Food]/[days]/8*[SumOfNormal],[Food]),[Food]))

I enclosed my access file, maybe somebody can help me, and by the way, i am trying to create a small salary program, to run the file jujst click Salary Calculation, then fill in the date data 01/02/2008 and 29/02/2008 and selec Salary Sheet.

On the Salary Report Sheet, you will notice 2 "Food/ Other Allowance" field. the one on the left is my unbound field Food1 while the other one is my bound field Food, computation differs when you go to Page2, now i need my Food1 Field to compute the Sum and not the Food Field

 

[jackryan]

any help please?

 

[Deleted member 30250]

Instead of doing the calculations in your report, add Expression fields to your query, so your query has every field you want to report, only the totals need to be a report function.

e.g.
JoinedThisPeriod: IIf([Date of Joining]<=Forms![Salary Calculation]!Text0," ",[Date of Joining])

I see you have 'days' as an invisible field, which you then refer to in your Food_1 calculation - refer to Forms![Salary Calculation]!Text5 instead of 'days'. And why not ditch the Text5, Expr2 stuff and call them meaningful names while you're at it?

At a very early stage of learning Access I read somewhere that all the work should go into queries, not reports and forms, and once I'd convinced myself of that I found life much easier. Get the query right, then the report will be easy-peasy.

 

[jackryan]

I already tried creating an Expression field inside the query and let the query handles the formula, I broke my If-Then-Else statement into 3 parts

FoodQ: IIf([Food]>0,[Food]/[Expr2]/8*[SumOfNormal])
NormalHrs: IIf([SumOfNormal]<>Max([SumOfNormal]),FoodQ,[Food])
Food1: IIf([Date of Joining] Between [Forms]![Salary Calculation]![Text0] And [Forms]![Salary Calculation]![Text2],[Food],NormalHrs)

but I get an error

Circular reference by FoodQ

 

[Deleted member 30250]

I'm confused! Please could you explain in English what the rules are to calculate 'Food/Other Allowance', and let's see if we can disentangle this?

 

[jackryan]

Hello,

Thank you for your interest in helping me on my problem,


My Food/Other Allowance calculation is like this

there are certain employee that have Allowance, and others have not, now if the employee have no Allowance then
[Food]=[Food]/[Days]/8*[SumOfNormal]
else
[Food]=[Food]

then the SumOfNormal is actually Sum of Normal Working Hours
if SumOfNormal is not equal to Max(SumOfNormal) then (Food)

and most of all the system has to check the Joining date of the employee if he arrived on this month or on the previous month, only those person arrived at the same payroll month will have different computation as their allowance will be computed from their Joining Date. My

[Forms]![Salary Calculation]![Text0] = 1st Day of the Month

[Forms]![Salary Calculation]![Text2]= last Day of the Month


Here is my VB If-Then-Else statement

If([Date of Joining] Between [Forms]![Salary Calculation]![Text0] And [Forms]![Salary Calculation]![Text2] then
[Food]=[Food]
Else
If([SumOfNormal]<>Max([SumOfNormal]) Then
If([Food]>0 then
[Food]=[Food]/[Days]/8*[SumOfNormal]
Else
[Food]=[Food]
Endif
Else
[Food]=[Food]
Endif
Endif


Thank you very much again.

 

[Deleted member 30250]

First of all, you have a number of table and field names that are, to say the least, dangerous, because they are Access reserve words. I suggest you change the following ones I've spotted, there may be more:

Table called name - how about StaffNames, and change the name field to StaffName or similar
In table advances paid, change Date to AdvDate or similar
In ENTERY, change NAME to EntName or similar
In Labour Hours change Date to WorkDate or similar
In PLUMBING change name to PlumbName or similar

In particular, having fields called 'name' can result in some very peculiar events!

I'm still not clear about your calculations. The reason I persist is that getting all the calculations done in your query really is the right way to build a report. Ignore your report for the moment, get your query to show every column you want to report. When you are satisfied that it is calculating everything correctly, only then go to your report - you can simply pick the fields for the detail lines, and =Sum() for the totals.

Is this correct?
If Staff Master.Food Allowance is zero, food allowance to be paid is zero, otherwise:
AllowanceDays = number of days in report period, or number of days from joining to end of report period, whichever is fewer.
MaxNormalHours = AllowanceDays x 8
Food allowance to be paid = Staff Master.Food Allowance x Normal hours actually worked / MaxNormalHours
In other words, the staff member gets a proportion of their food allowance calculated as the number of normal working hours actually worked to the maximum normal working hours possible in the working days in the month (or fewer days if they joined during the month).

If I got that right, the problem is (I think) that you were referencing Max(SumOfNormal) when you tried to calculate this in your query - the max of every record in the query. If you can use my MaxNormalHours instead, you should be ok.

 

[Deleted member 30250]

Now I'm getting nervous because (a) jackryan hasn't replied and (b) nor has anyone else!

Have you made any progress, Jack? Has everybody else looked at this one and thought I'm barking up the wrong tree (or just plain barking)? Or do the throngs of far-more-expert-than-I-am forum members think I'm on the right track?

 

[jackryan]

Now I'm getting nervous because (a) jackryan hasn't replied and (b) nor has anyone else!

Have you made any progress, Jack? Has everybody else looked at this one and thought I'm barking up the wrong tree (or just plain barking)? Or do the throngs of far-more-expert-than-I-am forum members think I'm on the right track?

I'm sorry if I hasn't been able to reply, I'm at the jobsite yesterday and I'm not able to check your suggestion.

I need to reference my Max(SumOfNormal), I'm only curios because why is it that I am able to get my formula to work inside the Report, and not inside Query. I only copy the same formula.

Also my formula has to check first if the employee's Joining Date is inside the current monthly payroll, if its true then, it needs to check if he completed the whole working hours [SumOfNormal]<>Max([SumOfNormal], then it has to check if it have Allowance, and if it's true then [Food]=[Food]/[Days]/8*[SumOfNormal], else everything is [Food]=[Food]

I'm sorry but the formula is really confusing, but hopefully, a solution will be made for this. I'm also planning if I'll transfer everything to VB but its another work, and for this I'm just trying to modify and correct the computation done previously by the person who created this file.

Thank you very much again.

 

[jackryan]

any more help please?