Jump to content
  • 0

Show distinct records


Question

I have a Teacher, Enrollment, and course tables.  The enrollments tie to teacher with teacher ids and courses with course ids.  The teacher may have multiple enrollment for one course.  I want to create a view to be used in a datapage that would list once the Teacher and Course.  How would I go about this?

 

Here is what it looks like now and want it to only have 1 record for Bob sanders, but because he has 2 enrollments in his class, it lists twice.  Any Ideas?

Courses_Course                                                      Personnel_Full_Name

English Language Arts, Grade 7                     Bob Sanders

English Language Arts, Grade 7                     Bob Sanders

English, Language and Composition, AP   Christopher Baker

 

Thanks,

Chris

Link to post
Share on other sites

6 answers to this question

Recommended Posts

  • 0

Hi @cbaker10,

You would need to use separate tables:

Table 1 for courses with assigned teachers

Table 2 for enrollments 

These tables should have a one-to-many relationship.

For your report, you should use Table 1 and Calculated field with SQL expression if you would like to display a count of enrollments from Table 2.

Alternatively, you can use your existing View and Collapsible Group of records by teacher name.

Here is the link to an article about :

https://howto.caspio.com/datapages/reports/advanced-reporting/data-grouping/

Hope this helps.

Regards,

vitalikssssss

 

 

Link to post
Share on other sites
  • 0
11 hours ago, cbaker10 said:

I'm getting closer.  Can I use a select statment on a View instead of a table?

 

Chris

Hi Chris,

I'm afraid you can't do Select Statement in Views itself however you can reference VIEW in an SQL statement just by placing   _v_     before the view name. For instance:

SELECT DISTINCT Firstname FROM _v_NameOfYourView where ID=[field:ID]

 

-Franchiser-

Link to post
Share on other sites
  • 0

So I am using SELECT DISTINCT([@field:Courses_Course]) FROM  _v_Teacher_Courses WHERE Personnel_ID=target.[@field:Personnel_ID] which works, but it lists the name of the first course for all courses instead of each individual course.  The second column is the calculated column and the last column is the actual course.  I am so close....any ideas?

 

Bob Sanders English Language Arts, Grade 7 2017-2018 3 English Language Arts, Grade 7
  Bob Sanders English Language Arts, Grade 7 2017-2018 3 English Language Arts, Grade 7
  Bob Sanders English Language Arts, Grade 7 2017-2018 3 English Language Arts, Grade 8
  Christopher Baker English, Language and Composition, AP 2017-2018 2 English, Language and Composition, AP
  Christopher Baker English, Language and Composition, AP 2017-2018 2 English, Literature and Composition, AP

 

 

Link to post
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.

Guest
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.

Loading...
×
×
  • Create New...