Select Total in form

[Guest]

I have a form and subform where I would like to total number of hours for
each employee based on three categories: Vacation, Personal, Other. I would
like to have three texboxes reflecting the sum for each category. Any
suggestions on how to do this?

 

[Steve Schapel]

Linda,

My guess here would be to put 3 unbound textboxes in the Footer section
of the subform, and set their Control Source properties to something
like this...
=Sum(IIf([Category]="Vacation",[Hours],0))

If you still need more explicit help with this, please post back with a
more detailed explanation of your data, maybe with examples.

 

[Guest]

I tried your example and it didn't work.

I am creating a database to keep track of Vacation and Personal Time for 20
employees. I have created three tables: Employees, Time, Time Categories.
The
Employees table contains EmployeeID, Name and Vacation Beginning Balance and
Personal Time Beginning Balance. The Time table contains Date, Time
Category, Hours Taken. The Time Categories table contains TimeCatID,
TimeCatName. For example the ID for Vacation is 1. I want to create a form
where you could view and enter Time Taken. At the bottom of the form, I
would like to tell me what the ending balance is for each Time Category. I
am not sure how to write an expression that will add the hours for each
category and then have it subtract that value from the corresponding
beginning balances. Any suggestions?

Linda,

My guess here would be to put 3 unbound textboxes in the Footer section
of the subform, and set their Control Source properties to something
like this...
=Sum(IIf([Category]="Vacation",[Hours],0))

If you still need more explicit help with this, please post back with a
more detailed explanation of your data, maybe with examples.

--
Steve Schapel, Microsoft Access MVP

I have a form and subform where I would like to total number of hours for
each employee based on three categories: Vacation, Personal, Other. I would
like to have three texboxes reflecting the sum for each category. Any
suggestions on how to do this?

 

[Steve Schapel]

Linda,

To add the total hours for each category, it is pretty much as I
suggested. Can you please give me the exact expression you tried, which
you said "didn't work", and can you also please say exactly what "didn't
work" means (i.e. error message, incorrect result, etc)? Can you also
please give some specific examples of your data? Thanks.

As for the remaining balance, there are two approaches. In the textboxes
on the subform, you can refer to the beginning balance from the main
form, for example like this...
[Parent]![Vacation Beginning Balance]
Or, you can have a separate textbox on the main form, where you subtract
the subform, totals from the main form opening balances.

By the way, as an aside, 'date' and 'time' are Reserved Words (i.e. have
a special meaning) in Access, and as such should not be used as the name
of a field or control or database object. I would recommend changing
this if you can.

 

[Guest]

Steve,

I changed the reserved words in my database and tried my expression again
and it worked! Thank you for your patience and help.

Linda

Linda,

To add the total hours for each category, it is pretty much as I
suggested. Can you please give me the exact expression you tried, which
you said "didn't work", and can you also please say exactly what "didn't
work" means (i.e. error message, incorrect result, etc)? Can you also
please give some specific examples of your data? Thanks.

As for the remaining balance, there are two approaches. In the textboxes
on the subform, you can refer to the beginning balance from the main
form, for example like this...
[Parent]![Vacation Beginning Balance]
Or, you can have a separate textbox on the main form, where you subtract
the subform, totals from the main form opening balances.

By the way, as an aside, 'date' and 'time' are Reserved Words (i.e. have
a special meaning) in Access, and as such should not be used as the name
of a field or control or database object. I would recommend changing
this if you can.

--
Steve Schapel, Microsoft Access MVP

I tried your example and it didn't work.

I am creating a database to keep track of Vacation and Personal Time for 20
employees. I have created three tables: Employees, Time, Time Categories.
The
Employees table contains EmployeeID, Name and Vacation Beginning Balance and
Personal Time Beginning Balance. The Time table contains Date, Time
Category, Hours Taken. The Time Categories table contains TimeCatID,
TimeCatName. For example the ID for Vacation is 1. I want to create a form
where you could view and enter Time Taken. At the bottom of the form, I
would like to tell me what the ending balance is for each Time Category. I
am not sure how to write an expression that will add the hours for each
category and then have it subtract that value from the corresponding
beginning balances. Any suggestions?