Jump to content
  • 0

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



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)

Link to comment
Share on other sites

3 answers to this question

Recommended Posts

  • 0

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)

Link to comment
Share on other sites

  • 0

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. 

Link to comment
Share on other sites

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.
Note: Your post will require moderator approval before it will be visible.

Answer this question...

×   Pasted as rich text.   Paste as plain text instead

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.

  • Create New...