ExecSQL does not (seem to) recognise foreign details
I have several ExecSQL calculations on a table (Let's call it Table Two as I set it up second) which reference a single detail which is brought in via relationship and calculated field from a foreign table (addresses). Table Two is the problem.
I have another table (let's call it Table One as I set it up first), set up in a similar way, which references the same foreign table above in the same way. Again, the same single detail is pulled through onto this table.
The code is pretty simple - I just want to count clients per county who have information on this table:
Select COUNT(ClientID)from TableTwo where
County=? <<<<< This is a calculated field brought in via the relationship to the table I have the SQL on
This code works perfectly on Table One, but Table Two, it doesn't distinguish between counties - instead of say, a count of 3, I get a count of 0.
1) Why would this work perfectly for table one, but not table two?
2) I cannot see that ExecSQL has an issue using calculated fields in its own syntax because it's worked perfectly on Table One?
I'm pretty stuck on this as the two tables are virtually identical in make up and are identical in relationship to the address table.