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

Query Calculation

P: 19
Hi,

I am trying to create an expression within a MS Access Query, but am having some issues.

I am using the following code to add together two values from 2 tables. But the problem is that if a value is not available from one table it is not included in the calculation and there for being displayed as a null.

SELECT Tbl_Supplier.[Supplier Name], Sum(Nz(Tbl_Contract_Maintenance.[Value of Contract],0)) AS [Value of Maintenance Contracts], Sum(Nz(Tbl_Contract_General_Head.[Value of Contract],0)) AS [Value of General Contracts], Sum((Tbl_Contract_Maintenance.[Value of Contract])+(Tbl_Contract_General_Head.[Value of Contract])) AS Expr1, Count(Tbl_Supplier.[Supplier Name]) AS [Number of Maintenance Contracts]

FROM (Tbl_Supplier LEFT JOIN Tbl_Contract_General_Head ON Tbl_Supplier.[Supplier ID] = Tbl_Contract_General_Head.[Supplier ID]) LEFT JOIN Tbl_Contract_Maintenance ON Tbl_Supplier.[Supplier ID] = Tbl_Contract_Maintenance.[Supplier ID]

GROUP BY Tbl_Supplier.[Supplier Name]

ORDER BY Sum((Tbl_Contract_Maintenance.[Value of Contract])+(Tbl_Contract_General_Head.[Value of Contract])) DESC;


I basically wanna display the following:

Value 1 Value 2 Total
1 2 3
2 2 4
2 3 5
4 2 6
4 0 4*
0 3 3*
0 3 3*

* This is the part that i can not get to display!

Have i explained it ok?

Can anyone help?

Thanks

James
Jan 8 '08 #1
Share this Question
Share on Google+
6 Replies


Megalog
Expert 100+
P: 378
Replace your expression code with this (in SQL view):

Sum((Iif(isnull([Tbl_Contract_Maintenance].[Value of Contract],0,[Tbl_Contract_Maintenance].[Value of Contract])))+(Iif(isnull([Tbl_Contract_General_Head].[Value of Contract],0,[Tbl_Contract_General_Head].[Value of Contract])))) AS Expr1

Hopefully that should work for you. It will check to see if either of the summed values is null. If it is null, it'll use a zero in the sum operation, else it will use the actual value.
Jan 8 '08 #2

P: 19
Hi,

Thanks for your reply, it makes sense, but a little unsure which part of the code i should be replacing.

Thanks



Replace your expression code with this (in SQL view):

Sum((Iif(isnull([Tbl_Contract_Maintenance].[Value of Contract],0,[Tbl_Contract_Maintenance].[Value of Contract])))+(Iif(isnull([Tbl_Contract_General_Head].[Value of Contract],0,[Tbl_Contract_General_Head].[Value of Contract])))) AS Expr1

Hopefully that should work for you. It will check to see if either of the summed values is null. If it is null, it'll use a zero in the sum operation, else it will use the actual value.
Jan 8 '08 #3

Megalog
Expert 100+
P: 378
You'd want to replace this:

Sum((Tbl_Contract_Maintenance.[Value of Contract])+(Tbl_Contract_General_Head.[Value of Contract])) AS Expr1

with the text I gave above.
Jan 8 '08 #4

P: 19
I have tried that, but it keeps reporting a syntax error (missing operator) in query expression.

at the moment my code looks like:

SELECT Tbl_Supplier.[Supplier Name], Sum(Nz(Tbl_Contract_Maintenance.[Value of Contract],0)) AS [Value of Maintenance Contracts], Sum(Nz(Tbl_Contract_General_Head.[Value of Contract],0)) AS [Value of General Contracts], Sum((Iif(isnull([Tbl_Contract_Maintenance].[Value of Contract],0,[Tbl_Contract_Maintenance].[Value of Contract])))+(Iif(isnull([Tbl_Contract_General_Head].[Value of Contract],0,[Tbl_Contract_General_Head].[Value of Contract]))) AS Expr1, Count(Tbl_Supplier.[Supplier Name]) AS [Number of Maintenance Contracts]
FROM (Tbl_Supplier LEFT JOIN Tbl_Contract_General_Head ON Tbl_Supplier.[Supplier ID] = Tbl_Contract_General_Head.[Supplier ID]) LEFT JOIN Tbl_Contract_Maintenance ON Tbl_Supplier.[Supplier ID] = Tbl_Contract_Maintenance.[Supplier ID]
GROUP BY Tbl_Supplier.[Supplier Name]
ORDER BY Sum((Tbl_Contract_Maintenance.[Value of Contract])+(Tbl_Contract_General_Head.[Value of Contract])) DESC


Is there anything i have missed off?

Thanks again
Jan 8 '08 #5

Megalog
Expert 100+
P: 378
Just looking over the code again, I sure complicated it more than it needed to be... I should've used the Nz function instead of the drawn out IIf/Isnull, and the Sort By needed to be updated as well.

Let's try:

Expand|Select|Wrap|Line Numbers
  1. SELECT Tbl_Supplier.[Supplier Name], Nz(Tbl_Contract_Maintenance.[Value of Contract],0) AS [Value of Maintenance Contracts], Nz(Tbl_Contract_General_Head.[Value of Contract],0) AS [Value of General Contracts], Sum((Nz(Tbl_Contract_Maintenance.[Value of Contract],0))+(Nz(Tbl_Contract_General_Head.[Value of Contract],0))) AS [Value Total], Count(Tbl_Supplier.[Supplier Name]) AS [Number of Maintenance Contracts]
  2.  
  3. FROM (Tbl_Supplier LEFT JOIN Tbl_Contract_General_Head ON Tbl_Supplier.[Supplier ID] = Tbl_Contract_General_Head.[Supplier ID]) LEFT JOIN Tbl_Contract_Maintenance ON Tbl_Supplier.[Supplier ID] = Tbl_Contract_Maintenance.[Supplier ID]
  4.  
  5. GROUP BY Tbl_Supplier.[Supplier Name]
  6.  
  7. ORDER BY Sum((Nz(Tbl_Contract_Maintenance.[Value of Contract],0))+(Nz(Tbl_Contract_General_Head.[Value of Contract],0))) DESC;
If that doesnt work, then hopefully someone better than I will pick up where we've both gone wrong...
Jan 8 '08 #6

MMcCarthy
Expert Mod 10K+
P: 14,534
Try this and see how you get on ...
Expand|Select|Wrap|Line Numbers
  1. SELECT Tbl_Supplier.[Supplier Name], 
  2. Sum(Nz(Tbl_Contract_Maintenance.[Value of Contract], 0)) AS [Value of Maintenance Contracts], 
  3. Sum(Nz(Tbl_Contract_General_Head.[Value of Contract], 0)) AS [Value of General Contracts], 
  4. ([Value of Maintenance Contracts]+[Value of General Contracts]) AS [Value Total], 
  5. Count(Tbl_Supplier.[Supplier Name]) AS [Number of Maintenance Contracts]
  6. FROM (Tbl_Supplier LEFT JOIN Tbl_Contract_General_Head 
  7. ON Tbl_Supplier.[Supplier ID] = Tbl_Contract_General_Head.[Supplier ID]) 
  8. LEFT JOIN Tbl_Contract_Maintenance 
  9. ON Tbl_Supplier.[Supplier ID] = Tbl_Contract_Maintenance.[Supplier ID]
  10. GROUP BY Tbl_Supplier.[Supplier Name]
  11. ORDER BY Sum(Nz(Tbl_Contract_Maintenance.[Value of Contract],0))+Sum(Nz(Tbl_Contract_General_Head.[Value of Contract],0)) DESC;
  12.  
Jan 9 '08 #7

Post your reply

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