crosstab - name two or three columns and then OTHER

[Guest]

how do i bunch everything else as Other.

i have earning codes, i went two or three of them to show and then the rest
to be bunched as other. if this is possible, i want to know if i can also
group the others into other1, other2 etc..

thanks,

sam

 

[John Nurick]

Hi Sam

The only way I know to do this is to use a calculated field which returns
distinct values for the codes you want but the same value for all the rest.

For instance, if the SQL view of the query now looks something like this:

TRANSFORM
SELECT qryByCounty.County, Count(qryByCounty.OAID) AS [Total Of OAID]
FROM qryByCounty
GROUP BY qryByCounty.County
PIVOT qryByCounty.DecileScore
;

and the possible values of DecileScore are 1..10, you could change the PIVOT
clause to

PIVOT
SWITCH(DecileScore=1,"1",DecileScore=2,"2",DecileScore=3,"3", -1,"Other")

The Switch function takes pairs of arguments, and evaluates the first in
each pair from left to right until it finds one that is True; it then
returns the value of the second argument in the pair. The -1 before "Other"
is always true. For more flexible grouping, you can do stuff like

SWITCH(DecileScore=1,"1",DecileScore=2,"2",DecileScore<=6,"3 to
6", -1,"Over 6")


See Help for more.

 

[Guest]

should work for me. didn't get to try it yet.

very useful,

thanks a lot,

sam

Hi Sam

The only way I know to do this is to use a calculated field which returns
distinct values for the codes you want but the same value for all the rest.

For instance, if the SQL view of the query now looks something like this:

TRANSFORM
SELECT qryByCounty.County, Count(qryByCounty.OAID) AS [Total Of OAID]
FROM qryByCounty
GROUP BY qryByCounty.County
PIVOT qryByCounty.DecileScore
;

and the possible values of DecileScore are 1..10, you could change the PIVOT
clause to

PIVOT
SWITCH(DecileScore=1,"1",DecileScore=2,"2",DecileScore=3,"3", -1,"Other")

The Switch function takes pairs of arguments, and evaluates the first in
each pair from left to right until it finds one that is True; it then
returns the value of the second argument in the pair. The -1 before "Other"
is always true. For more flexible grouping, you can do stuff like

SWITCH(DecileScore=1,"1",DecileScore=2,"2",DecileScore<=6,"3 to
6", -1,"Over 6")


See Help for more.

how do i bunch everything else as Other.

i have earning codes, i went two or three of them to show and then the
rest
to be bunched as other. if this is possible, i want to know if i can also
group the others into other1, other2 etc..

thanks,

sam

 

[Guest]

Use a select query ahead of the crosstab like this --
SELECT Data.Question,
IIf([Status]="Active",[Status],IIf([Status]="DONE",[Status],"Other")) AS
CODE, Data.amt
FROM Data;

Hi Sam

The only way I know to do this is to use a calculated field which returns
distinct values for the codes you want but the same value for all the rest.

For instance, if the SQL view of the query now looks something like this:

TRANSFORM
SELECT qryByCounty.County, Count(qryByCounty.OAID) AS [Total Of OAID]
FROM qryByCounty
GROUP BY qryByCounty.County
PIVOT qryByCounty.DecileScore
;

and the possible values of DecileScore are 1..10, you could change the PIVOT
clause to

PIVOT
SWITCH(DecileScore=1,"1",DecileScore=2,"2",DecileScore=3,"3", -1,"Other")

The Switch function takes pairs of arguments, and evaluates the first in
each pair from left to right until it finds one that is True; it then
returns the value of the second argument in the pair. The -1 before "Other"
is always true. For more flexible grouping, you can do stuff like

SWITCH(DecileScore=1,"1",DecileScore=2,"2",DecileScore<=6,"3 to
6", -1,"Over 6")


See Help for more.

how do i bunch everything else as Other.

i have earning codes, i went two or three of them to show and then the
rest
to be bunched as other. if this is possible, i want to know if i can also
group the others into other1, other2 etc..

thanks,

sam