Here's my SQL - need help with one last statement to get a calculation

[dcorleto]

Have the following mess of SQL code that creates a query based on several tables and other queries:

SELECT SubProjects.Code, SubProjects.Client, Val(Nz(Sum(Budget.AvidHours),0)) AS SumOfAvidHours, Val(Nz(Sum(Budget.AvidSub),0)) AS SumOfAvidSub, Val(Nz(Sum(Budget.GraphicsHours),0)) AS SumOfGraphicsHours, Sum(Budget.GraphicsSub) AS SumOfGraphicsSub, Sum(Budget.DubsSub) AS SumOfDubsSub, Val(Nz(Sum(Budget.Total),0)) AS SumOfTotal, SubProjects.Project, SubProjects.Budget, SubProjects.FEE, SubProjects.OOP, SubProjects.Active, SubProjects.Date, SubProjects.TTLedithours, SubProjects.TTLgraphicshours, QryInvoices.SumOfAmount
FROM (Budget RIGHT JOIN SubProjects ON Budget.Subcode = SubProjects.Code) LEFT JOIN QryInvoices ON SubProjects.Code = QryInvoices.Code
GROUP BY SubProjects.Code, SubProjects.Client, SubProjects.Project, SubProjects.Budget, SubProjects.FEE, SubProjects.OOP, SubProjects.Active, SubProjects.Date, SubProjects.TTLedithours, SubProjects.TTLgraphicshours, QryInvoices.SumOfAmount;



I would like to add one more expression to the query that would be the following calculation:

OOP-QryInvoices.SumOfAmount-SumOfTotal: Val(Nz(Sum(Budget.Total),0))

This returns an error when I try to run the query. Can anyone please help to tell me how to get this last calculation. Everything else works fine though I am a noob and I'm sure it could all be much cleaner. Thanks in advance.

 

[PHV]

Like this ?
OOP-QryInvoices.SumOfAmount-Val(Nz(Sum(Budget.Total),0)) AS SomeAlias

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886