Simplify this SQL statement?

  • Thread starter Thread starter pat o.
  • Start date Start date
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.
 
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)));
 
Back
Top