AnsweredAssumed Answered

How to count matching records in two related tables

Question asked by tcwaters on Feb 28, 2018
Latest reply on Mar 1, 2018 by tcwaters

I'm building a database that has three tables: High Schools, Applications, and Presentations. Both Applications and Presentations are lists of students.  The idea is to get a count of how many students who are in the presentation table are also in the application table.  Ing other words, did a presentation lead to students applying. I've created a layout, based on HighSchool with two portals- one showing related students/presentation, and one with related students/application. Using conditional formatting, I have names that match highlighted. But I also want a calc field in the HighSchool table that holds the number of these matches. So, I think this is what I'm trying to do: Count records in related application records where the name matches a name in the related presentation records.


The conditional formatting uses this calc: PatternCount ( Presentations_NameMatch::PresentationMatch ; Application 2::PresentationMatch)


But if I use the same calc as a field in the High School table I get "0" when the number should be "3". 


Do I need to be using ExecuteSQL since what I want to do includes a "where"?