How to show zero values in budget v actual query

[Dobletz]

I have a database that contains a series of budgets for different construction projects in separate tables and a master table for expenses in another table. I am trying to develop a 'budget v actual' query that can be output to a report for each project. I have created a separate query for 'budget' and a query for expenses for each project. I am trying to now develop a query that would pull from each of the foregoing that would show 'budget' v. 'actual'. However, if any of the budget line items or expenses line items are $0, then they do not appear in either Query. I need all budget line items to appear even if the budget is $0 and all expense line items to appear even if expenses to date are $0. Thanks in advance!

[Rabbit]

Use an outer join instead of a inner.

[Dobletz]

Ok. Thanks. That works! Now, how do I have the Query show $0 where there are no expenses to date for the budget line item. I want the report to show $0 when there are no expenses to date.

[Rabbit]

Use the Nz() function to convert the nulls to 0.

[Dobletz]

I must be a "nube". Can't seem to get this to work. I'm trying to get "SumofTotalInvoiceAmount" to show $0 when there are no expenses to date. How would I modify the SQL?

Here is the SQL:

SELECT [Budget - 151 - Tower Road Query].BudgetCode, [Budget - 151 - Tower Road Query].BudgetCodeName, [Budget - 151 - Tower Road Query].OriginalBudget, [Budget - 151 - Tower Road Query].CurrentProjectedBudget, [151ExpensestoDate].SumOfTotalInvoiceAmount
FROM [Budget - 151 - Tower Road Query] LEFT JOIN 151ExpensestoDate ON [Budget - 151 - Tower Road Query].BudgetCode = [151ExpensestoDate].BudgetCode
GROUP BY [Budget - 151 - Tower Road Query].BudgetCode, [Budget - 151 - Tower Road Query].BudgetCodeName, [Budget - 151 - Tower Road Query].OriginalBudget, [Budget - 151 - Tower Road Query].CurrentProjectedBudget, [151ExpensestoDate].SumOfTotalInvoiceAmount;

[Rabbit]

Use the Nz() function to convert the nulls to 0.

Nz([151ExpensestoDate].SumOfTotalInvoiceAmount, 0)

Also, as a side note, I don't know why you're using a group by when you're not running an aggregate.

[Dobletz]

Thanks for all your help on this.

I eliminated the group by and modified the SQL as indicated below. Now I only get results for budget line items that have expenses to date.

SELECT [Budget - 151 - Tower Road Query].BudgetCode, [Budget - 151 - Tower Road Query].BudgetCodeName, [Budget - 151 - Tower Road Query].OriginalBudget, [Budget - 151 - Tower Road Query].CurrentProjectedBudget, [151ExpensestoDate].SumOfTotalInvoiceAmount
FROM [Budget - 151 - Tower Road Query] LEFT JOIN 151ExpensestoDate ON [Budget - 151 - Tower Road Query].BudgetCode = [151ExpensestoDate].BudgetCode
WHERE ((([151ExpensestoDate].SumOfTotalInvoiceAmount)=Nz([151ExpensestoDate].[SumOfTotalInvoiceAmount],0)));

[Rabbit]

When did I say to use the WHERE clause? You only use the WHERE clause when you're trying to filter out records. What you want to do is display them differently. The SELECT clause is what controls how something is displayed.

[Dobletz]

Right you are. Moved Nz to SELECT and it works perfectly. Thanks so much for your help. Cheers!

[Rabbit]

No problem.