Sum the total values in a subform

[Guest]

Hello,
I am making a employee timesheet database. I have a form with two subforms.
The main form is for employees to select the date. The first subform is where
they select their name. The third subform is the time subform where employees
enter a project number, # of hours worked, and what work they did. It is
possible for employees to work on more than one project in a day so I would
like to make a field (in the time subform footer??) that adds up the hours
the employee worked that day so they can use that is a check to make sure
they entered the right amount of time for that day.

Any suggestions on how best to do this would be greatly appreciated.

thanks,

 

[Marshall Barton]

I am making a employee timesheet database. I have a form with two subforms.
The main form is for employees to select the date. The first subform is where
they select their name. The third subform is the time subform where employees
enter a project number, # of hours worked, and what work they did. It is
possible for employees to work on more than one project in a day so I would
like to make a field (in the time subform footer??) that adds up the hours
the employee worked that day so they can use that is a check to make sure
they entered the right amount of time for that day.

It seems like all you need is a text box in the time subform
footer section that uses an expression like:
=Sum(hours)

 

[Guest]

It seems like all you need is a text box in the time subform
footer section that uses an expression like:
=Sum(hours)

Hi Marshall,
I tried that and it works fine in the subform if only the subform is open.
But when it is in the main form I get an error message. I think the reason it
works in the subform itself is it sums the total hours for all days and
projects, but when it is put into the main form it can't figure out what
values it should be adding (only the values for the day).

To further explain my setup I have a main form where employee selected the
date, this has a subform which an employee selects his/her name from a drop
down menu of all employees. Within this subform is the time subform where
employees enter the project # and hours worked on a project. Within this
subform I want to sum the hours for the day as a check for employees to make
sure they put in the correct amount of hours for that day.

My table/ relationship structure is set up like this:

Date Table Work Table Time Table

Date ID (1-365) ----> Date ID Time ID
Month Work ID ---------> Work ID
Date (01-jan-07) Employee ID Project Code
Day of Week Hours
Pay Period Activity Code
Comment

I'm not sure if any of this makes sense but I hope it explains my situtation
better.

Thanks,

 

[Marshall Barton]

I tried that and it works fine in the subform if only the subform is open.
But when it is in the main form I get an error message. I think the reason it
works in the subform itself is it sums the total hours for all days and
projects, but when it is put into the main form it can't figure out what
values it should be adding (only the values for the day).

To further explain my setup I have a main form where employee selected the
date, this has a subform which an employee selects his/her name from a drop
down menu of all employees. Within this subform is the time subform where
employees enter the project # and hours worked on a project. Within this
subform I want to sum the hours for the day as a check for employees to make
sure they put in the correct amount of hours for that day.

My table/ relationship structure is set up like this:

Date Table Work Table Time Table

Date ID (1-365) ----> Date ID Time ID
Month Work ID ---------> Work ID
Date (01-jan-07) Employee ID Project Code
Day of Week Hours
Pay Period Activity Code
Comment

Your comment that it sums all dates implies that the time
subform is displaying all dates. I was assuming that it
used the WorkID in the Link Master/Child properties so it
only displayed the date selected in the work subform. If
that's not what you are doing, please explain more about how
the subforms are linked and what they display from their
record source.

 

[Guest]

Your comment that it sums all dates implies that the time
subform is displaying all dates. I was assuming that it
used the WorkID in the Link Master/Child properties so it
only displayed the date selected in the work subform. If
that's not what you are doing, please explain more about how
the subforms are linked and what they display from their
record source.

Hi Marshall,
You are correct - when the subform is open on its own (not part of main
form) it does show all the dates (and the sum function works fine). But when
I open the main form that has the two subforms embedded in it the sum doesn't
work. You are correct the work subform (where employees select their name) is
linked to the time subform by work ID, the work subform is also linked to the
main form (Where the date is selected) by the DateID.

So when the Main form is opened I have a form where you select the date,
which is linked to a subform (via dateid) with a drop down menu for employee
names which is linked (via workID) to a subform where employees enter their
project and time.
Here is an example of what my main form looks like.

Main Form
Jan 1, 2007
Work Subfrom
Employee: Karl Zimmer
Time Subform:
Project Hours Acitivity Code Description
703 1.2 2 reporting
777 1.7 3 mapping
789 4.3 2 reporting

So in the end I want to be able to add up all the hours worked by Karl
Zimmer on Jan 1, 2007, so in this case it would be a total of 7.2 hours
worked.

Sorry I'm so confusing, but so you know your help and patience is greatly
appreciated.

Thanks,

 

[Marshall Barton]

Hi Marshall,
You are correct - when the subform is open on its own (not part of main
form) it does show all the dates (and the sum function works fine). But when
I open the main form that has the two subforms embedded in it the sum doesn't
work. You are correct the work subform (where employees select their name) is
linked to the time subform by work ID, the work subform is also linked to the
main form (Where the date is selected) by the DateID.

So when the Main form is opened I have a form where you select the date,
which is linked to a subform (via dateid) with a drop down menu for employee
names which is linked (via workID) to a subform where employees enter their
project and time.

Here is an example of what my main form looks like.

Main Form
Jan 1, 2007
Work Subfrom
Employee: Karl Zimmer
Time Subform:
Project Hours Acitivity Code Description
703 1.2 2 reporting
777 1.7 3 mapping
789 4.3 2 reporting

So in the end I want to be able to add up all the hours worked by Karl
Zimmer on Jan 1, 2007, so in this case it would be a total of 7.2 hours
worked.

A text box in the Time subform footer section with he
expression =Sum([Hours]) will do exactly that.

You said "it doesn't work", but I have no way of determining
what actually happened. I suspect that it is working, but
you have an inapproporiate Format propert setting based on a
probably inapproriate data type. Please explain exactly
what did happen when you entered those values in the Hours
field along with the data type of the Hours field (Date,
integer, Text, Single, etc). I'm guessing that the field is
a Date field, when I think Currency would probably be best
and the Format should just be 0.0

 

[Guest]

Hi Marshall,

Thanks for your help. Upon reviewing the format I noticed I had the formula
to sum the hours of the work table instead of just sum[hours]. So I changed
it to what you said and it worked like a darn. Thanks a lot for your patience
and help.

Take care,