Help with Row Totals in Crosstab??

Results 1 to 2 of 2

Thread: Help with Row Totals in Crosstab??

  1. #1
    Join Date
    Dec 1969

    Default Help with Row Totals in Crosstab??

    Hi. I&#039;m struggling with something that is probably pretty simple. I have the following query in Access...<BR><BR>SELECT DATA2.OMC_YN, Count(DATA2.ID) AS Base<BR>FROM DATA2<BR>WHERE (((DATA2.RV_BODY) Is Not Null))<BR>GROUP BY DATA2.OMC_YN;<BR><BR>...which gives me the following output...<BR><BR>OMC_YN Base<BR>1 13<BR>2 8<BR><BR>Ideally, I would like to have this output "transposed", so it would look like this...<BR><BR>[NULL] 1 2<BR>Base 13 8<BR><BR>...I tried playing with a TRANSFORM query to get it into this format, but I&#039;m struggling with how to get the count of all the "RV_BODY" values rolled up into the "Base" row. My gut says this should be simple, but I&#039;m stumped. Any suggestions?<BR><BR>thanks- Tim<BR><BR><BR><BR><BR><BR>

  2. #2
    Join Date
    Dec 1969

    Default RE: Help with Row Totals in Crosstab??

    You need to have 1 field as being a Row Header<BR><BR>I added 1 field called RV_Maker...<BR>you can swap it for something else just to see...<BR><BR>TRANSFORM Count(DATA2.ID) AS BASE<BR>SELECT DATA2.RV_Maker, Count(DATA2.ID) AS [Total Of BASE]<BR>FROM DATA2<BR>WHERE (((DATA2.RV_BODY) IS NOT NULL))<BR>GROUP BY DATA2.RV_Maker<BR>PIVOT DATA2.OMC_YN;<BR><BR>For 3 rv_makers you should get somehting like this<BR><BR>RV_Maker_____Total of Base_______1__2__3__4__5<BR>Maker#1_____ 10_________________4__1__2__2__1<BR>Maker#2_____ 5__________________1__1__1__1__1<BR>Maker#3_____ 9__________________2__2__1__2__2<BR><BR>YOu must supply a row column ie: rv_maker....<BR><BR>Hope it helps.<BR>

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts