I am new to SQL processes in Filemaker. I have a table “DataArts” (DA) and a table “Participation_Records” (PR). I want to show the number of students enrolled in the program “Creative Dramatics”, which draws data from the PR table and shows it, along with similar calculations in similar fields for 10-12 other programs, in the DA table. The SQL formula is in the DA table, and the information re the total number of students comes from the PR table.
The formula works correctly if I simply type “Creative Dramatics” after WHERE Program_Name =. But I would like the calculation to be dynamic in that it will search for participants in whatever program is listed in the field “DArts:: Program_Name”. This is because the program names change from session to session.
What am I doing incorrectly? All attempts, such as enclosing “DataArts::DArts_Program_Name” in parentheses, or in single or double quotes, return either nothing or a question mark.
Many thanks for any help! Here is the formula I wrote: (Note: I first tried it with just "DArts_Program_Name" since the field is in the DA table, but that, of course, was interpreted as text by the formula.)
SELECT SUM (Number_Partic)
WHERE Program_Name = DataArts::DArts_Program_Name