sum in form and sub form

K

Kelly

Ok, I know this is a no brainer... i just cant seem to wrap my brain around
it...
I have a very simple form to track our media distribution...cds, tapes,
mailed, instore, special request... each field is bascially a number field
where we input the amount of each one... the only difference is the special
request... that is a subform because we need more detail, but has a Qty
field. All I want to do is create a running total in a total field... I
think i need to create a query, but I dont know where to begin... I


Kelly
 
S

Sprinks

Hi, Kelly.

If you'd like to have a total of all the subform records associated with the
parent record, place a summary field in the subform's footer:

=Sum([Qty])

On the more general topic of summing, counting, averaging, etc., this is
done in a Totals query (View, Totals from query design view). Any field or
combination of fields for which you'd like a sum is dragged to the grid, with
its Total row set to "Group By". Drag the field you'd like to summarize to
the grid, and set its Total row to "Sum". To select a subset of all the
records, drag the criteria field to the grid, set its Total row to "Where"
and enter the criteria.

For example, say you wanted to calculate a total qty for each customer whose
customer number is greater than 1000. The resulting SQL would be something
like:

SELECT YourTable.CustomerNumber, Sum(YourTable.Qty) AS SumOfQty
FROM YourTable
WHERE (((YourTable.CustomerNumber)>1000))
GROUP BY YourTable.CustomerNumber;

You can also do averages, counts, min/max, and several others.

Hope that helps.
Sprinks
 
A

Arvin Meyer

Create footers for the forms (or subforms) and add a text box (you can set
the height of both the text box and the footer to 0, if you like.

Set the control source of the text box to (substituting your control's name
of course):

=Sum([MyQuantityField])

You can show this text box or set another one in a better visual location
equal to it.
--
Arvin Meyer, MCP, MVP
Microsoft Access
Free Access Downloads
http://www.datastrat.com
http://www.mvps.org/access
 
K

Kelly

Ok, got my total in my subform...

#1 so how do I know add that total to a field in the main form (i.e. special
request totals)...

#2Then I want to add 3 fields (i.e. mailed, instore, special request) and
total that in another field... is it even possible in a form, or will i have
to do it in a query. Keeping the integreity of the numbers is important
because eventually I will have to print reports...

--
Kelly


Sprinks said:
Hi, Kelly.

If you'd like to have a total of all the subform records associated with the
parent record, place a summary field in the subform's footer:

=Sum([Qty])

On the more general topic of summing, counting, averaging, etc., this is
done in a Totals query (View, Totals from query design view). Any field or
combination of fields for which you'd like a sum is dragged to the grid, with
its Total row set to "Group By". Drag the field you'd like to summarize to
the grid, and set its Total row to "Sum". To select a subset of all the
records, drag the criteria field to the grid, set its Total row to "Where"
and enter the criteria.

For example, say you wanted to calculate a total qty for each customer whose
customer number is greater than 1000. The resulting SQL would be something
like:

SELECT YourTable.CustomerNumber, Sum(YourTable.Qty) AS SumOfQty
FROM YourTable
WHERE (((YourTable.CustomerNumber)>1000))
GROUP BY YourTable.CustomerNumber;

You can also do averages, counts, min/max, and several others.

Hope that helps.
Sprinks


Kelly said:
Ok, I know this is a no brainer... i just cant seem to wrap my brain around
it...
I have a very simple form to track our media distribution...cds, tapes,
mailed, instore, special request... each field is bascially a number field
where we input the amount of each one... the only difference is the special
request... that is a subform because we need more detail, but has a Qty
field. All I want to do is create a running total in a total field... I
think i need to create a query, but I dont know where to begin... I


Kelly
Click to expand...
Click to expand...
 
Top