addition on a form

[Guest]

On the form I have contact information at the top. In the middle, I have a
subform. The form is used to enter stock certificate information for a
shareholders. The stockholder comes up in the top, and each share and its
information is in the subform. for example George Knight might be in the top,
and 4 certificates are listed in the subform. Assume each certificate has a
face value of 1,000 shares each. In the subform footer, I have a count
formular and a sum formular. The count returns a value of 4 and the sum
returns a value of 4,000. However, in addition, I would like to see, when I
am entering information, a count value and a sum value. for all the
shareholder records. Example, assume 40 share holders and 20,000 in total.
How can I get the total for all records, displayed on the form. I tried to
the sum and count formular in the main form footer, but it returns the same
value as in the subform footer, I get a #Name? message.

 

[Guest]

I create a sum query that I join in my main query and display in the main
form area.

 

[John Vinson]

On the form I have contact information at the top. In the middle, I have a
subform. The form is used to enter stock certificate information for a
shareholders. The stockholder comes up in the top, and each share and its
information is in the subform. for example George Knight might be in the top,
and 4 certificates are listed in the subform. Assume each certificate has a
face value of 1,000 shares each. In the subform footer, I have a count
formular and a sum formular. The count returns a value of 4 and the sum
returns a value of 4,000. However, in addition, I would like to see, when I
am entering information, a count value and a sum value. for all the
shareholder records. Example, assume 40 share holders and 20,000 in total.
How can I get the total for all records, displayed on the form. I tried to
the sum and count formular in the main form footer, but it returns the same
value as in the subform footer, I get a #Name? message.

Since the records you're counting and summing are not in fact
currently on the form, you'll need to use the DCount() and DSum()
functions. Let's say the table with the stock data is named tblStock
with a field FaceValue; you could put two textboxes in the subform
footer with Control Sources

=DCount("*", "[tblStock]")
=DSum("[FaceValue]", "[tblStock]")

Both of these functions accept an optional third parameter, to set the
criteria (if you want to exclude inactive clients for example).

John W. Vinson[MVP]

 

[Guest]

Thank you very much for this information.

Wnen using Dcount, how can you set the criteria to not count duplicate
record fields. Example. I want to use the Dcount funtion to count the number
of share holders, so the field is Shareholder Number. Some shareholders ave
several certificates, so the function sees:
Shareholder # Share Amount
1 1,000
1 2,000
1 3,000
2 1,000
3 500
when use Dcount, it returns 5. How can you use the criteria to return (only
count 1 once) 3.

Thanks

On the form I have contact information at the top. In the middle, I have a
subform. The form is used to enter stock certificate information for a
shareholders. The stockholder comes up in the top, and each share and its
information is in the subform. for example George Knight might be in the top,
and 4 certificates are listed in the subform. Assume each certificate has a
face value of 1,000 shares each. In the subform footer, I have a count
formular and a sum formular. The count returns a value of 4 and the sum
returns a value of 4,000. However, in addition, I would like to see, when I
am entering information, a count value and a sum value. for all the
shareholder records. Example, assume 40 share holders and 20,000 in total.
How can I get the total for all records, displayed on the form. I tried to
the sum and count formular in the main form footer, but it returns the same
value as in the subform footer, I get a #Name? message.

Since the records you're counting and summing are not in fact
currently on the form, you'll need to use the DCount() and DSum()
functions. Let's say the table with the stock data is named tblStock
with a field FaceValue; you could put two textboxes in the subform
footer with Control Sources

=DCount("*", "[tblStock]")
=DSum("[FaceValue]", "[tblStock]")

Both of these functions accept an optional third parameter, to set the
criteria (if you want to exclude inactive clients for example).

John W. Vinson[MVP]

 

[John Vinson]

Thank you very much for this information.

Wnen using Dcount, how can you set the criteria to not count duplicate
record fields. Example. I want to use the Dcount funtion to count the number
of share holders, so the field is Shareholder Number. Some shareholders ave
several certificates, so the function sees:
Shareholder # Share Amount
1 1,000
1 2,000
1 3,000
2 1,000
3 500
when use Dcount, it returns 5. How can you use the criteria to return (only
count 1 once) 3.

You may need another query, based on the Shareholder table, or a
SELECT DISTINCT query. AFAIK you can't do it easily within a single
call to DCount. I'm not sure what other criteria you want to apply,
but if you save a query selecting only the Shareholder #, with its
Unique Values property set to true, and using the same criteria as
your subform - you can use DCount() to count records in that query.

John W. Vinson[MVP]