Summary info from subform's data

[Dave the Wave]

I would like some help with creating a list of fields in the footer of the
main form based on data shown in the subform. The data inthe subform is
based on a dynamic SQL query created wihen the main form is opened. There
are 3 criteria combo boxes at the top of the main form which modify the
subforms SQL statement.

Will I need to calculate the summary information in the subform code, store
it in variables, then display it in the main form's footer? Should I move
all the summary information to the subform?

Any links to helpful web sites would be greatly appreciated.

Thanks

 

[Marshall Barton]

I would like some help with creating a list of fields in the footer of the
main form based on data shown in the subform. The data inthe subform is
based on a dynamic SQL query created wihen the main form is opened. There
are 3 criteria combo boxes at the top of the main form which modify the
subforms SQL statement.

Will I need to calculate the summary information in the subform code, store
it in variables, then display it in the main form's footer? Should I move
all the summary information to the subform?

I guess it depends on what you mean by "summary information"

Totals can be calculated in the subform's footer section by
using an aggregate function (Count, Sum, etc) in a text box
control source expression like =Sum(somefield).

That total can be displayed in a single or continuous
subform, but not in a datasheet view subform (which do not
display thier header/footer sections).

If youwant to display that total on the main form, then the
main form text box can use an expression:
=subformcontrolname.Form.totaltextboxname

 

[John Vinson]

I would like some help with creating a list of fields in the footer of the
main form based on data shown in the subform. The data inthe subform is
based on a dynamic SQL query created wihen the main form is opened. There
are 3 criteria combo boxes at the top of the main form which modify the
subforms SQL statement.

Will I need to calculate the summary information in the subform code, store
it in variables, then display it in the main form's footer? Should I move
all the summary information to the subform?

Any links to helpful web sites would be greatly appreciated.

Thanks

You can actually do quite a bit of calculation right in the subform
controls. Make sure the subform Footer is visible (in design mode
anyway); put textboxes in it with control sources such as

=Sum([fieldname])

to sum the value in that field (there are Avg(), Count(), etc.
functions as well).

Name a textbox txtSumOfX if you're summing the field X. Then on the
mainform, put another textbox with a control source

=subMySubform.Form!txtSumOfX

using the Name property of your subform control, to "relay" the
calculation from the subform onto the main form. The subform footer
can be shrunk to nothing or the textboxes made invisible if you don't
want to see the summary data twice.

John W. Vinson[MVP]

 

[Dave the Wave]

I would like some help with creating a list of fields in the footer of
the main form based on data shown in the subform. The data inthe
subform is based on a dynamic SQL query created wihen the main form is
opened. There are 3 criteria combo boxes at the top of the main form
which modify the subforms SQL statement.

Will I need to calculate the summary information in the subform code,
store it in variables, then display it in the main form's footer?
Should I move all the summary information to the subform?

Any links to helpful web sites would be greatly appreciated.

Thanks

You can actually do quite a bit of calculation right in the subform
controls. Make sure the subform Footer is visible (in design mode
anyway); put textboxes in it with control sources such as

=Sum([fieldname])

to sum the value in that field (there are Avg(), Count(), etc.
functions as well).

Name a textbox txtSumOfX if you're summing the field X. Then on the
mainform, put another textbox with a control source

=subMySubform.Form!txtSumOfX

using the Name property of your subform control, to "relay" the
calculation from the subform onto the main form. The subform footer
can be shrunk to nothing or the textboxes made invisible if you don't
want to see the summary data twice.

John W. Vinson[MVP]

Thanks John, that works for some of the information I need to add.
However, I also need to count the number of records that meet certain
criteria. For instance, the database is a collection of test results.
Some of the tests are actually retests and labeled accordingly. I need
to count how many of the records shown in a form are retests.

I tried using a iif statement in the control source of a text box
positioned in the footer, but it didn't work.

tblMainDataRetest is a Yes/No feild.

(in form footer) unbound text box control source=iif(tblMainDataRetest=-
1,count([tblMainDataID),)

Any suggestions?

Thanks in advance.

 

[John Vinson]

Thanks John, that works for some of the information I need to add.
However, I also need to count the number of records that meet certain
criteria. For instance, the database is a collection of test results.
Some of the tests are actually retests and labeled accordingly. I need
to count how many of the records shown in a form are retests.

The trick here is to include a calculated field in the Form's
Recordsource query:

IIF{Retest], 1, 0)

and Sum this value in the footer.

John W. Vinson[MVP]

 

[Dave the Wave]

Thanks John, that works for some of the information I need to add.
However, I also need to count the number of records that meet certain
criteria. For instance, the database is a collection of test results.
Some of the tests are actually retests and labeled accordingly. I need
to count how many of the records shown in a form are retests.

The trick here is to include a calculated field in the Form's
Recordsource query:

IIF{Retest], 1, 0)

and Sum this value in the footer.

John W. Vinson[MVP]

John:

I found an example in MS's archives. They show:
=Count(IIF([YesNoField],1,0))
When I tried this in the footer, it didn't work.

If I understand your directions correctly, I should create a text box -
tbxRetestNo- with a control source =IIF([retest],1,0) and put it in the
detail portion of the form. Then in the footer, place a second text box
with the control source =sum(tbxRetestNo).

 

[John Vinson]

If I understand your directions correctly, I should create a text box -
tbxRetestNo- with a control source =IIF([retest],1,0) and put it in the
detail portion of the form. Then in the footer, place a second text box
with the control source =sum(tbxRetestNo).

No. You did not read my directions correctly.

You can sum a *field in the form's Query* - you cannot sum a control
on the form.

Open the (sub) Form in design view. View its Properties. Click the ...
icon by the Recordsource property; if Access grumps about "creating a
query" say yes. In a vacant Field cell in the query type

IsRetest: IIF([retest], 1, 0)

You can then use =Sum([IsRetest])

in the footer.

John W. Vinson[MVP]

 

[Marshall Barton]

I found an example in MS's archives. They show:
=Count(IIF([YesNoField],1,0))
When I tried this in the footer, it didn't work.

Either that article is wrong or you mistyped it. The
expression can be any of several, but not that one.

Try:
=Count(IIF([YesNoField],1,Null))
or
=Sum(IIF([YesNoField],1,0))
or
=Abs(Sum([YesNoField]))

But John's idea is much less devious.