How to count and sum registries from a table and pass it to a control in a form?

[everterra]

Hello.

In Access 2013 I have a table to registry the presents in a condominium meeting.


Table Name: tbEntities


Fields:


tbEntId...tbPresent (yes / no)..........tbVotes

1.........yes...........................5


2.........no.............................3
3.........yes............................4
4.........no.............................6

I want to put in a form (like a navigation/menu form, with no souce data):

1. a control that count how many are present (= yes)

Ex: Appearances 2

2. a control that sum the votes of those present

Ex: 9 Votes

Can somebody help with this? Thanks.

[June7]

Is tbPresent a yes/no data type field?

Expression in textbox ControlSource and textboxes in form footer section:

=Count(IIf(tbPresent=True, 1))

=Sum(IIf(tbPresent=True, tbVotes, 0))

[everterra]

Thanks for your reply.


Yes, the field tbPresent is yes/no data type.


But the form is like a navigation/menu form with other source data...
After use your sugestion, gives me #Error.

[June7]

What do you mean 'like a navigation/menu'? Is this a form/subform arrangement? What you show looks like Single View. tbPresent is in the subform? Put the calcs into the footer of the subform.

[everterra]

Thanks again June7.


And no.
The form is based in the table tbAssembleias.
The Count field must show the results of a query based in the another table - tnEntidades or count what fields have "yes".
The Sum field must show the sum of the tbVotes field.


I have already a query who counts the presents and in the SQL view stays like:


SELECT Count(tbEntidades.tbAssPresente) AS ContaPresentes, tbEntidades.tbAssPresente
FROM tbEntidades
GROUP BY tbEntidades.tbAssPresente
HAVING (((Count(tbEntidades.tbAssPresente))=True) AND ((tbEntidades.tbAssPresente)=True));


But I can not figure how to put the result in the field Count.

[June7]

If the field is not included in the form RecordSource, then options:

1. subform bound to the aggregate query

2. listbox that uses the aggregate query as RowSource

3. domain aggregate function (DCount, DSum, etc)

[everterra]

Ok, because it seemed more apropriated I experimented with your option 2, and resulted.


Using a listbox, >Properties >Source >Source line, and using there the Constructor, I mounted a query.


For the fist list box to show the Count, the result was:


SELECT Count(tbEntidades.tbAssPresente) AS ContaPresentes, tbEntidades.tbAssPresente
FROM tbEntidades
GROUP BY tbEntidades.tbAssPresente
HAVING (((Count(tbEntidades.tbAssPresente))=True) AND ((tbEntidades.tbAssPresente)=True));


For the second list box to show the Sum, the result was:


SELECT Sum(tbFrações.tbFrPerc) AS SomaDetbFrPerc, tbEntidades.tbAssPresente
FROM tbFrações
INNER JOIN tbEntidades ON tbFrações.tbFrId = tbEntidades.tbFrações.Value
GROUP BY tbEntidades.tbAssPresente
HAVING (((tbEntidades.tbAssPresente)=True));


Many thanks for your help June7