Dcount can't make it work in a report on a query (1 Viewer)

[TiagoDM]

Hi, I have a report based on a query, which is based on a form. The SQL statement for the query, named [csCriancasTurmas], is

SELECT DISTINCTROW PlanoDiario.Presenças.Value, Turmas.Crianças.Value, Atividades.Atividade, Atividades.Docente, PlanoDiario.Data, PlanoDiario.Hora, PlanoDiario.[Licao nº], PlanoDiario.Sumário, PlanoDiario.Turma2
FROM Turmas INNER JOIN (Atividades INNER JOIN PlanoDiario ON Atividades.ID = PlanoDiario.Atividade) ON Turmas.ID = PlanoDiario.Turma2
WHERE (((PlanoDiario.Presenças.Value) Like ([Formulários]![Registo_teste_criancas_tudo]![Nome Próprio] & " " & [Formulários]![Registo_teste_criancas_tudo]![Aplelido])) AND ((Turmas.Crianças.Value) Like ([Formulários]![Registo_teste_criancas_tudo]![Nome Próprio] & " " & [Formulários]![Registo_teste_criancas_tudo]![Aplelido])));

and I want to count the values of [Atividade] inside the report. Translated to english, this is a students database in which each student has several activities. (Atividade -> Activities). In the report, it shows all activities and presences (Presenças -> Presences ... PlanoDiário -> Summary) and I would like to count the number of activities each student has. The "problem" (... i guess) is that the query is based on a form. If it wasn't and i had a "simple" dcount function it would been this way, right? :

=Dcount("csCriancasTurmas";"Atividade")

I tried many dcount strings .. here's the latest, which doesn't work

=DContar("csCriancasTurmas";"Atividade";"(([Planodiario.presenças]='" & [Formulários]![Registo_teste_criancas_tudo]![Nome Próprio] & " " & [Formulários]![Registo_teste_criancas_tudo]![Aplelido] & "') AND ([Turmas.Crianças]='" & [Formulários]![Registo_teste_criancas_tudo]![Nome Próprio] & " " & [Formulários]![Registo_teste_criancas_tudo]![Aplelido] & "'))")

Simplifying,

=DContar("queryname";"fieldwhichIwanttocount";"parameters")

with

Parameters = (([Planodiario.presenças.value]='"  & [Forms]![Registo_teste_criancas_tudo]![Nome Próprio] & "  " & [Forms]![Registo_teste_criancas_tudo]![Aplelido] &  "') AND ([Turmas.Crianças.value]='" &  [Formulários]![Registo_teste_criancas_tudo]![Nome Próprio] & " "  & [Formulários]![Registo_teste_criancas_tudo]![Aplelido] &  "'))")

I forgot to say that fields in the query and report, named [Planodiario.Presenças] and [Turmas.Crianças] are multiselection values :((((

I'm sorry the presentation of what i want to say is maybe a bit confusing... I just don't know what to do more.

Thank you,

Tiago

 

[vbaInet]

Did you know that you can:

1. GROUP BY student id
2. Put =Count([Atividade]) in a textbox in the Group Footer's section to get the count of Atividade per student.

?

 

[TiagoDM]

Did you know that you can:

1. GROUP BY student id
2. Put =Count([Atividade]) in a textbox in the Group Footer's section to get the count of Atividade per student.

?

Hi, yes, this way is simpler, thank you, but if my report is per student, how to count all the distinct activities one has made?

Ex: i have peter, who has participated in maths, portuguese, english and science. So, the total count of activities should be 4. How can I group by student if there is only one?

Thank you,


Tiago

 

[vbaInet]

I'm guessing you have fields for Maths, Portuguese, English and Science? Or do they appear as separate records?

 

[TiagoDM]

I'm guessing you have fields for Maths, Portuguese, English and Science? Or do they appear as separate records?

There's a form to create classes to a table and there's another to create the students within the classes to another table.... with multiple values .. so one class may have multiple students. ...

They're all together in several tables, by student. In the report, it has a query which filters the classes for a single student in which he/she has presences.

I have the report grouped by activity (maths, portuguese, and so on ... ) and I want to count the number of distinct activities, not the number of the presences.

mmm ...

Imagine that : table [classes] with fields [maths] ... [portuguese] ...
... and then another table [summaries] with fields [class] ... multifield [students] .... [writtensummary]

The query will check for student "peter" what classes and presences was he in..

Sorry i know its a bit confusing, but i'll post a pdf with the output of the report, if that helps.

Thankx

Tiago

 

[vbaInet]

Show us a screenshot of your tables in design view.

 

[TiagoDM]

I uploaded some screenshots of the tables here :

If u need something more, plz say.

Thankx

Tiago

 

[vbaInet]

Sorry, but I don't download from external sites. You can upload the images here.

How to display an image:
http://www.access-programmers.co.uk/forums/showthread.php?t=194680

How to upload a an image or a db:
http://www.access-programmers.co.uk/forums/showthread.php?t=140587

 

[TiagoDM]

Sorry ... the image files are attached

 

[vbaInet]

It's hard to uhnderstand anything because I don't speak Spanish.

I want to know about this table you talked about here:

"Imagine that : table [classes] with fields [maths] ... [portuguese] ... "

 

[TiagoDM]

Sorry ... It's best if I send you the DB, but I don't think i can... it's very large, even compressed. I don't have only table [classes] with [maths ... ], I have a table with classes names, another with activties (computers, maths, ...), and another with summaries (wich includes a field with multiple values -> names of the sudents). Sorry I didn't explain very well the first time, but I'm doing several things at the same time....

If U want to have a look at the Db, backend only is about 25Mb and the frontend is about 16Mb...

Thanks for your effort... even if you can't help more, thank you.

Tiago

 

[vbaInet]

If Maths, English, Spanish... etc are field names then you have a problem with your data structure. It's not properly normalised and it would be easy to get the count if it was.