By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
424,497 Members | 2,113 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 424,497 IT Pros & Developers. It's quick & easy.

How to show zero values in budget v actual query

P: 7
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!
Jun 25 '12 #1
Share this Question
Share on Google+
9 Replies


Rabbit
Expert Mod 10K+
P: 12,315
Use an outer join instead of a inner.
Jun 25 '12 #2

P: 7
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.
Jun 25 '12 #3

Rabbit
Expert Mod 10K+
P: 12,315
Use the Nz() function to convert the nulls to 0.
Jun 25 '12 #4

P: 7
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:

Expand|Select|Wrap|Line Numbers
  1. 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
  2. FROM [Budget - 151 - Tower Road Query] LEFT JOIN 151ExpensestoDate ON [Budget - 151 - Tower Road Query].BudgetCode = [151ExpensestoDate].BudgetCode
  3. 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;
Jun 25 '12 #5

Rabbit
Expert Mod 10K+
P: 12,315
Use the Nz() function to convert the nulls to 0.
Expand|Select|Wrap|Line Numbers
  1. 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.
Jun 25 '12 #6

P: 7
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)));
Jun 25 '12 #7

Rabbit
Expert Mod 10K+
P: 12,315
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.
Jun 25 '12 #8

P: 7
Right you are. Moved Nz to SELECT and it works perfectly. Thanks so much for your help. Cheers!
Jun 25 '12 #9

Rabbit
Expert Mod 10K+
P: 12,315
No problem.
Jun 25 '12 #10

Post your reply

Sign in to post your reply or Sign up for a free account.