Simplify this SQL statement?

P

pat o.

Can this statement be simplified? It seems like a "hack" but it works
for me. I'm just wondering if theres a more efficient way to do it.

The query lists all records in tblClients and the sum of curAmount for
the specified period.
 
P

Patrick

SELECT tblClients.intClientID, tblClients.strName,
Sum(tblCommissions.curAmount) AS SumOfcurAmount
FROM tblClients RIGHT JOIN tblCommissions ON tblClients.intClientID =
tblCommissions.intClientID
WHERE (((tblCommissions.datPeriod) Between
[Forms]![frmPrintReports]![txtPeriodStart] And
[Forms]![frmPrintReports]![txtPeriodEnd] Or (tblCommissions.datPeriod)
Is Null))
GROUP BY tblClients.intClientID, tblClients.strName
ORDER BY Sum(tblCommissions.curAmount) DESC
UNION SELECT tblClients.intClientID, tblClients.strName, 0 AS
SumOfcurAmount
FROM tblClients
WHERE tblClients.intClientID NOT IN (SELECT tblClients.intClientID
FROM tblClients RIGHT JOIN tblCommissions ON tblClients.intClientID =
tblCommissions.intClientID
WHERE (((tblCommissions.datPeriod) Between
[Forms]![frmPrintReports]![txtPeriodStart] And
[Forms]![frmPrintReports]![txtPeriodEnd] Or (tblCommissions.datPeriod)
Is Null)));
 
Top