Get sum from another table using SQL

Discussion created by wfgclapp on Nov 24, 2014
Latest reply on Nov 24, 2014 by dwelam

Trying to hack my way into SQL...


I have two tables: Customers and Salesmen.


Each Customer record has a number field called Cases and a field called sales_id


sales_id is also the unique id associated with records in Salesmen.


I need a Calculation field in Salesmen that is the sum of Cases for that salesman's customers.


I.e. I need to total the Cases field in Customers, but only for customer records that match the sales_id in Salesmen.


My Calculation field in Salesmen is called Tot_Cases and here's my calculation:


Tot_Cases=ExecuteSQL("SELECT SUM(Cases) FROM Customers WHERE sales_id=?" ; " " ; " " ; Salesmen:sales_id)


Pretty sure my hangup is not understanding the pieces of the WHERE statement. I've looked at several examples and tried various iterations but no joy.


Can anyone spot what I'm doing wrong?


THanks so much!