>>I’m stuck. I have a table with about 30 fields. Each contains numeric values ranging from 0-5. I need to COUNT each value, on each field. It should looks something like this:
>>Vals. Fld1 Fld2 ....etc.
>>0_____30____55_____….
>>1_____24____10_____….
>>2_____16____55_____….
>>3_____33____98_____….
>>4_____77____22_____….
>>5_____80____180____….
>>
>>
>>I want to be able to do this in the least typing possible, that’s why I thought of a query. Now, I know selecting the count is not the problem, but identifying the values of each count is what I can’t figure out.
>>thanks in advance
>
>Try following code ( I didn't test it)
>
>
>Select ;
> 0 As Vals, ;
> Sum(IIF(Fld1=0, 1, 0)) As Cnt1, ;
> Sum(IIF(Fld2=0, 1, 0)) As Cnt2, ;
> ....
> Sum(IIF(Fld30=0, 1, 0)) As Cnt30, ;
> From Mytable ;
>Union ;
>Select ;
> 1 As Vals, ;
> Sum(IIF(Fld1=1, 1, 0)) As Cnt1, ;
> Sum(IIF(Fld2=1, 1, 0)) As Cnt2, ;
> ....
> Sum(IIF(Fld30=1, 1, 0)) As Cnt30, ;
> From Mytable ;
>...
>Union ;
>Select ;
> 5 As Vals, ;
> Sum(IIF(Fld1=5, 1, 0)) As Cnt1, ;
> Sum(IIF(Fld2=5, 1, 0)) As Cnt2, ;
> ....
> Sum(IIF(Fld30=5, 1, 0)) As Cnt30, ;
> From Mytable ;
> Order By 1 ;
> Into Cursor Mycursor
>
>or
>
>
>FOR liVals=0 To 5
> lcCursor = "Mycursor" + Transfom(liVals)
> Select ;
> liVals As Vals, ;
> Sum(IIF(Fld1=liVals, 1, 0)) As Cnt1, ;
> Sum(IIF(Fld2=liVals, 1, 0)) As Cnt2, ;
> ....
> Sum(IIF(Fld30=liVals, 1, 0)) As Cnt30, ;
> From Mytable ;
> Into Cursor (lcCursor)
>ENDFOR
>
>and tnan combine 6 cursors Mycursor0-Mycursor5 into one cursor Mycursor
