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!