425,705 Members | 1,852 Online
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
6 Replies

 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

 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

 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 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]   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((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

 Expert Mod 10K+ P: 14,534 Try this and see how you get on ... Expand|Select|Wrap|Line Numbers 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],  ([Value of Maintenance Contracts]+[Value of General Contracts]) AS [Value Total],  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(Nz(Tbl_Contract_Maintenance.[Value of Contract],0))+Sum(Nz(Tbl_Contract_General_Head.[Value of Contract],0)) DESC;   Jan 9 '08 #7