ExecuteSQL or Count Calculation Issue, PLEASE HELP!
I'm trying to create a function that will count the number of records with a specific value in a related table, but only for records in the related table that are related to the record the calculation is in. I've tried using the ExecuteSQL function but I can't successfully create a relationship between the two tables within the SQL code that works. I've managed to count the number of records with the specific value, but it is always for all of them and not solely the ones that are related. Below is the function that I think should work based I what I've found online, but the result is always " ? "
ExecuteSQL ("select count (a.Indication) from Training_Aids a JOIN Sessions b. on a.fk_Session_Serial# = b.pk_Session_Serial# where a.Indication = ? " ; "" ; "" ; 1)
I have also tried creating a SelfJoin within the table that holds the records that needs to be counted and then doing a count within the SelfJoin table for the field I want. However, with the Count function there is no way (that I know of) to distinguish a specific value to count (like the CountIf function in Excel).
If anyone could provide any sort of help, I would be most appreciative! Thanks!