Displaying all fields regardless of result

[watsoneg5]

I have an issue where i have two joined table, one that has names and reference numbers and the other with just reference numbers and balances. There are about 5 unique names. I then join the tables and sum all the balances for each unique name that has a daily balance of greater then 1000. not every unique name will have any balances greater then 1000, so when they are summed and grouped those names will not be displayed in the results. Because i want to join these queries with other queries later i wish to also display the names that basically had 0 values, so that when combined with queries that are looking for say balances greater then 50, all names will be displayed.

I tried an iff statement that says that if the bal is not greater then say 1000 bal = 0. This works when i just want to sum the balances, but when i need to do more complicated arithmatic it is difficult. Is there another way to do this so i don't have a bunch of messy IIF statements.

Here's a sample code below using the IIF statement:

SELECT dbo_Dimension.ACCT_NAME, Sum(IIf(CDbl(dbo_view!CurrentBookPrincipal)<=85000,dbo_view!CurrentBookPrincipal,0)) AS [<85k]
FROM dbo_Dimension INNER JOIN dbo_view ON dbo_Dimension.CVG_4_PLANNING_ACCT_NAME=dbo_view.CVGPlanningAccountName
GROUP BY dbo_Dimension.ACCT_NAME;

Thanks

 

[boblarson]

If you have a bunch of different conditions, I usually find it best to create a custom function to return the appropriate values based on the field information passed to it.

 

[watsoneg5]

Can you give me an example? An example of a problem that i would have from the above example would be if i tried to change the sum to avg. You would see that the result would be off because if would be averaging 0's for fields that didn't meet the balance criteria.

 

[PeterOC]

Why not have 2 queries? One for the sum as above and one for the average with the clause 'Where [<85k] <> 0'.

 

[boblarson]

Well, you would have something like:

Function CalcMyField(curVal As Currency, strOperation As String) As Currency
   curMyVal As Currency

   Select Case strOperation
   Case "Whatever"
      curMyVal =  .... do one calculation here ...
   Case "Whatever Else"
      curMyVal     ...  do another here .....
   End Select
   
   CalcMyField = curMyVal
   
End Function

and then you would call it in your SQL

Select CalcMyField(dbo_view!CurrentBookPrincipal, "Whatever") As SomeName ...etc.

 

[watsoneg5]

Thanks, I haven't tried the formula yet, but the problem with the two queries is that at the end of the second query you are again left with just the names of fields that are not equal to 0, I still want those names to show up with 0 in the results of the second query. Otherwise i could just skip the 2 queries and just get the average first.