report based on a view but need 1 column to be the count of records of only 1 table in the view

[Billp]

The view has 3 tables,  example structure consistent with the question  they are missedClass with 2 FK's one to Class and the Other to student

ViewMissedClass is: select ClassName, ParticipantFullName, DateMissed from MissedClass right outer join Class on (id's) join Student on (ids)

The result I want will include a row for each student who missed a class, for now lets assume they miss one and are kicked out

ClassName                     StudentName                  DateMissed                    Numberofpeople in class

Basic Woodworking    Joan Doe                          6/1/23                              15

Basic Woodworking    John Doe                         6/15/23                            15

Autoshop                                                                                                                         23

The report is already including 1 row for the ClassName even if there is no one in the class as well as being able to select more than one class using some date language,  But in order to validate that the classes we are looking up even have anyone in them it helps to have that count. 

The only field I am interested in solving is the NumberofPeople in class column, the rest is working fine (outer joins of course)

[Volomeister]

Hi @Billp

Could you specify which table has NumberofPeople field?

[Billp]

no table hs NumberOfPeople field.  In SQL that would be select count(id) from class where classname = <parameter>;

I am simplifying this from a normalized table set to a sinble table with the class name repeated for each student, in the real system there is a class (classId, className) and roster(rosterId, classId, participantId).   In which case the sql version of the subquery to get the numberofpeople would be select count(participantID) where roster.classId = <the ID of the class you want to know how many people are registered for)

[CoopperBackpack]

Hello @Billp,

Since a View can only include the fields from the tables and there is no NumberOfPeople value in any table, you need to count the NumberOfPeople on the Report, for example.

To count the correct value in the Calculated Field you need to have the values in the View that allow correct counting.

For example, if each className is unique, you may count by className. If it is not unique, you may include classId too.

The statement example:

SELECT COUNT(id) FROM table_name WHERE classId = target.[@field:classId]

I am not sure that I got the issue that you have. If the suggestion above in not the case, please provide more details.