Multiple Totals

[MANDI201]

Hi,

Thanks for your help so far has helped a lot.

I have been tasked with setting up a database to record holiday and sickness. I want to be able to total these independantly of each other. I am using a pick list to chose from holiday or sick with a total of days taken but I am unable to get it to distinguish between holiday or sick. it will add up whether I choose holiday or sick.
Total days: IIf(([half_day]=Yes),"-0.5","-0")+([Date_end]-[date_start])+1


What I need it to say is IF [reason for absence]=Holiday then give me a total but IF [reason for absence]=Sickness ignore

I can then do another box for sickness totals asking the reverse i.e. IF [reason for absence]=sickness then give me a total but IF [reason for absence]=holiday then ignore

I hope you understand what I mean. There is probably a much simpler way of doing this but I am still only at intermediate stage!

Any help you can give me would be appreciates

Many thanks

MANDI

 

[neileg]

Where do you want these totals?

It would be normal to run a query that selects the records that match your requirement. You would put in the criteria row for your reason column "Holiday" or "Sick". You could make this a totals query and it would do your adding up too.

 

[MANDI201]

Multiple totals

Thanks will try that.

I presume I would need to do 2 different queries giving me 2 totals to work from. Will I be able to put this into a form to show the totals?

I will try thanks for your help.

MANDI

 

[MANDI201]

Cracked it!

Thanks 4 tip I did query with 3 total columns one for HOL one for Flex and one for SICK it then adds up each one separately. Great!

Total Days Hol:Iif(([Reason for absence]="holiday",Ii(([half-day]=yes),"-0.5,"-0")+)[date_end]-[date_start]))+1

I repeated this formula for the other 2 (flex and Sick)

Works fine Many thanks for your help.


MANDI

 

[Pat Hartman]

Only text values should be surrounded by quotes. Remove the quotes around your numeric values.

Total Days Hol:Iif(([Reason for absence]="holiday",IIi(([half-day]=yes),-0.5,-0)+)[date_end]-[date_start]))+1