Here's a really great way to validate your OQL query and make maximum use of the autocomplete features offered by Mendix: create a Data set.
Judging from your initial OQL query, am I correct in stating that you do have an association between CustomerAccount and SalesOrder? There's two things I noticed:
This means that for example
FROM Customer.CustomerAccount c
FROM Customer.CustomerAccount AS c
Another example is that this
INNER JOIN SalesOrderProcessing.SalesOrder_CustomerAccount/SalesOrderProcessing.SalesOrder AS s
INNER JOIN c/SalesOrderProcessing.SalesOrder_CustomerAccount/SalesOrderProcessing.SalesOrder AS s
Thanks very much for that.
But looking at the answer, would I be correct in assuming that there is no way to jump from linked table to linked table by comparing fields, as you can in regular SQL?
This message arises because SalesOrder_Depot is not an association in entity Customer. Try my untested first guess:
SELECT DISTINCT COUNT(*) AS IntValue FROM SalesOrder AS s
INNER JOIN SalesOrderProcessing.SalesOrder_Depot/Business.Depot as d
INNER JOIN SalesOrderProcessing.SalesOrder_SalesRep/Business.SalesRep as r
INNER JOIN SalesOrderProcessing.SalesOrder_Customer/Customer.CustomerAccount as c
WHERE s.OrderDate >= $LowerDate and s.OrderDate <= $UpperDate
AND s.SalesOrderStatus IN ('Allocated', 'Dispatch')
AND d.DepotName IN ('Bournemouth','Dover')
AND r.SalesRepShortName in ('Charlie Angel','FWalsh')