Totals for distinct records within a subform

  • Thread starter Thread starter Karl
  • Start date Start date
K

Karl

I have a form that contains a subform (data sheet form) in
which two fields contain the pertinent information. The
first field contains the hour of production and the second
contains the number of units. I need to total the number
of units by the hour of production. There will be several
records for each hour within this subform. The final
place for the totals will be on the main form. How can I
accomplish this?
 
In the Footer section of your subform, create a textbox--
I'll call it txtTotProduction--and type something like the
following for the Control Source:

=Sum([hour]*[units])

In the main form create a textbox and enter the following
for the Control Source:

=[frmSubform].[Form]![txtTotProduction]
 
These types of analytics are best performed via
functions. The DSum() function will get you what you
need, although what it lacks in effeciency, it makes up
for in simplicity. I've assumed your [hour of production]
is a value between 1 and 24, and the field joining your
main form to your subform is [job number].

=DSum("[number of units]","[table name]","[hour of
production]=1 And [job number]=" & [Forms]![FormName]!
[ControlName])

you can use something similar to what's above as the
control source of your 'totals' text box controls. Just
substitute the hardcoded value (1 above) as appropriate.
Hope this helped. good luck!
 
I have a form that contains a subform (data sheet form) in
which two fields contain the pertinent information. The
first field contains the hour of production and the second
contains the number of units. I need to total the number
of units by the hour of production. There will be several
records for each hour within this subform. The final
place for the totals will be on the main form. How can I
accomplish this?
Click to expand...

I'm not quite sure what you're asking. Is the "hour" a Date/Time
field, or a number? What do you want the subform to look like? Could
you post an example?

If you want to display subtotals interspersed with the actual data,
it's going to be rather difficult; you may need a UNION query between
the raw production information and a TOTALS query summing that same
information, and the result will certainly not allow you do update the
record. This would be much easier on a Report, which allows multiple
levels of groups and totals.
 
Back
Top