Access Query Issue - Sum Across a Row 1

[laina222]

Need help!!!

I have a table that I need to create a total across a row instead of the typical summing the values of a column. Basically what I want to do is add a two new fields to the table for each row - one called "Total" and one called "MaxTotal".

So in each row I have 50 Criteria - Criteria1, Criteria2...Criteria50. These range in value from 0-2, though some of these will be null, and so I need to set the null values to zero, but only to get the "Total" field - when I open the form I don't want the null values populated. The null values are extremely important to get the "MaxTotal" field because they should not be counted.

So let's say this is one record, and lets say that there are really only 5 Criteria

Field Value
Criteria1 2
Criteria2 0
Criteria3 NULL
Criteria4 2
Criteria5 1

Then I want a Total field which basically says:
Criteria1+Criteria2+Criteria3+Criteria4+Criteria5, Which would be 2+0+0+2+1 = 5 (The second 0 is in place of the null value).

Total 5

Then, after I get the total field, I want a field to count all of the Criteria fields that were populated with non-null values, called "MaxTotal". So in the example above, MaxTotal = 8 (Because Criteria1, Criteria2, Criteria4 and Criteria5 are all populated and not Null, and the Max value that could be in each of those is 2, so 2*4 = 8)

MaxTotal 8

When I finally get these two values, then I can easily calculate a TotalPercentage by doing Total/MaxTotal.

Thanks!

 

[r937]

select iif(isnull(Criteria1),0,Criteria1)
      +iif(isnull(Criteria1),0,Criteria1)
      +iif(isnull(Criteria1),0,Criteria1)
      +iif(isnull(Criteria2),0,Criteria2)
      +iif(isnull(Criteria3),0,Criteria3)
      +iif(isnull(Criteria4),0,Criteria4)
      +iif(isnull(Criteria5),0,Criteria5)
      +iif(isnull(Criteria6),0,Criteria6)
      +iif(isnull(Criteria7),0,Criteria7)
      +iif(isnull(Criteria8),0,Criteria8)
      +iif(isnull(Criteria9),0,Criteria9)
      +iif(isnull(Criteria10),0,Criteria10)
      +iif(isnull(Criteria11),0,Criteria11)
      +iif(isnull(Criteria12),0,Criteria12)
      +iif(isnull(Criteria13),0,Criteria13)
      +iif(isnull(Criteria14),0,Criteria14)
      +iif(isnull(Criteria15),0,Criteria15)
      +iif(isnull(Criteria16),0,Criteria16)
      +iif(isnull(Criteria17),0,Criteria17)
      +iif(isnull(Criteria18),0,Criteria18)
      +iif(isnull(Criteria19),0,Criteria19)
      +iif(isnull(Criteria20),0,Criteria20)
      +iif(isnull(Criteria21),0,Criteria21)
      +iif(isnull(Criteria22),0,Criteria22)
      +iif(isnull(Criteria23),0,Criteria23)
      +iif(isnull(Criteria24),0,Criteria24)
      +iif(isnull(Criteria25),0,Criteria25)
      +iif(isnull(Criteria26),0,Criteria26)
      +iif(isnull(Criteria27),0,Criteria27)
      +iif(isnull(Criteria28),0,Criteria28)
      +iif(isnull(Criteria29),0,Criteria29)
      +iif(isnull(Criteria30),0,Criteria30)
      +iif(isnull(Criteria31),0,Criteria31)
      +iif(isnull(Criteria32),0,Criteria32)
      +iif(isnull(Criteria33),0,Criteria33)
      +iif(isnull(Criteria34),0,Criteria34)
      +iif(isnull(Criteria35),0,Criteria35)
      +iif(isnull(Criteria36),0,Criteria36)
      +iif(isnull(Criteria37),0,Criteria37)
      +iif(isnull(Criteria38),0,Criteria38)
      +iif(isnull(Criteria39),0,Criteria39)
      +iif(isnull(Criteria40),0,Criteria40)
      +iif(isnull(Criteria41),0,Criteria41)
      +iif(isnull(Criteria42),0,Criteria42)
      +iif(isnull(Criteria43),0,Criteria43)
      +iif(isnull(Criteria44),0,Criteria44)
      +iif(isnull(Criteria45),0,Criteria45)
      +iif(isnull(Criteria46),0,Criteria46)
      +iif(isnull(Criteria47),0,Criteria47)
      +iif(isnull(Criteria48),0,Criteria48)
      +iif(isnull(Criteria49),0,Criteria49)
      +iif(isnull(Criteria50),0,Criteria50) 
          as Total
     , (
       iif(isnull(Criteria1),0,1)
      +iif(isnull(Criteria1),0,1)
      +iif(isnull(Criteria1),0,1)
      +iif(isnull(Criteria2),0,1)
      +iif(isnull(Criteria3),0,1)
      +iif(isnull(Criteria4),0,1)
      +iif(isnull(Criteria5),0,1)
      +iif(isnull(Criteria6),0,1)
      +iif(isnull(Criteria7),0,1)
      +iif(isnull(Criteria8),0,1)
      +iif(isnull(Criteria9),0,1)
      +iif(isnull(Criteria10),0,1)
      +iif(isnull(Criteria11),0,1)
      +iif(isnull(Criteria12),0,1)
      +iif(isnull(Criteria13),0,1)
      +iif(isnull(Criteria14),0,1)
      +iif(isnull(Criteria15),0,1)
      +iif(isnull(Criteria16),0,1)
      +iif(isnull(Criteria17),0,1)
      +iif(isnull(Criteria18),0,1)
      +iif(isnull(Criteria19),0,1)
      +iif(isnull(Criteria20),0,1)
      +iif(isnull(Criteria21),0,1)
      +iif(isnull(Criteria22),0,1)
      +iif(isnull(Criteria23),0,1)
      +iif(isnull(Criteria24),0,1)
      +iif(isnull(Criteria25),0,1)
      +iif(isnull(Criteria26),0,1)
      +iif(isnull(Criteria27),0,1)
      +iif(isnull(Criteria28),0,1)
      +iif(isnull(Criteria29),0,1)
      +iif(isnull(Criteria30),0,1)
      +iif(isnull(Criteria31),0,1)
      +iif(isnull(Criteria32),0,1)
      +iif(isnull(Criteria33),0,1)
      +iif(isnull(Criteria34),0,1)
      +iif(isnull(Criteria35),0,1)
      +iif(isnull(Criteria36),0,1)
      +iif(isnull(Criteria37),0,1)
      +iif(isnull(Criteria38),0,1)
      +iif(isnull(Criteria39),0,1)
      +iif(isnull(Criteria40),0,1)
      +iif(isnull(Criteria41),0,1)
      +iif(isnull(Criteria42),0,1)
      +iif(isnull(Criteria43),0,1)
      +iif(isnull(Criteria44),0,1)
      +iif(isnull(Criteria45),0,1)
      +iif(isnull(Criteria46),0,1)
      +iif(isnull(Criteria47),0,1)
      +iif(isnull(Criteria48),0,1)
      +iif(isnull(Criteria49),0,1)
      +iif(isnull(Criteria50),0,1) 
        ) * 2 as MaxTotal      
  from ...

rudy

http://r937.com/

SQL Consulting

 

[r937]

whoops, copy/paste error (see how easy it is?), i have Criteria1 three times and it should only be once

rudy

http://r937.com/

SQL Consulting

 

[laina222]

Wow, thanks! The second part works perfectly, but the first part I'm getting exponential results (i.e., 2.2222222E+15). Any clue on why this could be happening?

 

[laina222]

Oops! Never mind - it was all about the data types - needed to set them to "Number". Duh.

 

[JonFer]

You could also use this syntax to avoid the IIf() overhead:

-(IsNull(Criteria1)+IsNull(Criteria2)+...)

IsNull returns -1 for true and 0 if false.

John

http://www.interautomateconsulting.com

 

[r937]

thanks, jonfer, but that would only work for the MaxTotal, not for the Total

and wouldn't isnull also have overhead?

rudy

http://r937.com/

SQL Consulting

 

[JonFer]

IsNull by itself obviously won't have as much overhead as IIf + IsNull.

I guess I would use nz(Criteria##,0) for Total then.


John

http://www.interautomateconsulting.com

 

[MichaelRed]

Although the proposed soloution 'works', there is an uderlying (and un-addressed) issue re normalization. While there may be exceptions, the concept od having 50 criteria(N) fields in a record is a glaring example.


MichaelRed

 

[MichaelRed]

Although the proposed soloution 'works', there is an uderlying (and un-addressed) issue re normalization. While there may be exceptions, the concept od having 50 criteria(N) fields in a record is a glaring example.






MichaelRed

 

[r937]

this normalization issue was addressed in thread701-934002

we've already lambasted laina222 for the poor design

rudy

http://r937.com/

SQL Consulting