Query help

[Unicon]

Dear all,
Please see the attachments as we are paying staffs as 26 days basis. If more than 26 days it will be considered as OT eg. (If 28 days they worked than it should consider as 2 OT). I tried to put some formula but facing problem like my query is showing those staffs also 26 days, who has worked less than 26 days.

Please advise and help me out.

 

[CBrighton]

What is your desired result?

Are you looking for a positive number for people who have done overtime and a 0 for those who have not? If so, an Iif statement should do the job.

Depending on what you want this may or may not be useful but I'd drop both of the calculated fields in your screenie and have something like:

OverTime: Iif([1]>26,[1]-26,0)

This should populate the field with the amount of OT hours they have done or 0 if they have done 26 or fewer hours.

 

[Unicon]

Thanks again CBrighton ,

That is exactly what I am looking for OT but I need to show Normal duty too where if someone is working for 28 days than OT will show 2 days but in an another hand I want to show Normal Total = 26.

 

[CBrighton]

If you have another field something like

NormalTotal: 26

then you can substitute any reference to 26 with NormalTotal.

As well as giving you a column with 26 for each record this has the added bonus that just changing the value in the NormalTotal column will change all the calculations so if the standard working hours change in the future it's easier to update.

 

[Unicon]

Thanks CBrighton,

Done, You are not much that bad what forum says .

God bless u.

 

[Unicon]

Hi CBrighton,

Sorry I am bothering you again

I am recently using attendance in ms access and my table structure is like (Please find the attachment) I want to create a separate table where I can store different amount for different designation (which I can calculate later on basis of attendance) but here the problem is even for same designation I have a different salary depends on site. I think a lot how to create that one but I am not able to find out the solution. Please help me out
Your feedback on this matter should be highly appreciated .