Q: Null values and NZ() in crosstab query

Collapse
This topic is closed.
X
X
Collapse
 
  • Page of 1
  • Time
  • Show
Clear All
new posts
Previous template Next
  • John
    #1

    Q: Null values and NZ() in crosstab query

    I've read several prior posts in this group about using nz() to
    convert null values to zero; however, I'm not sure how/where to
    implement this function in my crosstab query.

    The crosstab query (qryPromoFilm_N etCM_Crosstab) uses another query
    (qryPromo_NetCM ) as its source. The crosstab is used to show revenue
    spread out through the twelve months. I need the months with null
    values to have a "0" value.

    This is the SQL for the crosstab query (qryPromoFilm_N etCM_Crosstab):
    TRANSFORM Avg(qryPromo_Ne tCM.curNetCM) AS AvgOfcurNetCM
    SELECT qryPromo_NetCM. strPromoTitle, qryPromo_NetCM. strTitle
    FROM qryPromo_NetCM
    GROUP BY qryPromo_NetCM. strPromoTitle, qryPromo_NetCM. strTitle
    PIVOT qryPromo_NetCM. lngFiscalMonthI D In (1,2,3,4,5,6,7, 8,9,10,11,12);

    This is the SQL for the crosstab query's source (qryPromo_NetCM ):
    SELECT tblPromos.strPr omoTitle, tblFilms.strTit le,
    tblPromo_NetCM. lngFiscalYear, tblFiscalMonths .strFiscalMonth ,
    tblPromo_NetCM. curNetCM, tblPromo_NetCM. lngFiscalMonthI D,
    tblPromo_Films. lngPromoFilmID
    FROM tblPromos INNER JOIN ((tblFilms INNER JOIN tblPromo_Films ON
    tblFilms.lngFil mID = tblPromo_Films. lngFilmID) INNER JOIN
    (tblFiscalMonth s INNER JOIN tblPromo_NetCM ON
    tblFiscalMonths .lngFiscalMonth ID = tblPromo_NetCM. lngFiscalMonthI D) ON
    tblPromo_Films. lngPromoFilmID = tblPromo_NetCM. lngPromoFilmID) ON
    tblPromos.lngPr omoID = tblPromo_Films. lngPromoID
    ORDER BY tblPromos.strPr omoTitle, tblFilms.strTit le,
    tblPromo_NetCM. lngFiscalYear, tblPromo_NetCM. lngFiscalMonthI D;


    Any suggestions on where/how to implement the nz() function?

    TIA,
    John
    Tags: None
    • DFS
      #2
      Re: Null values and NZ() in crosstab query


      "John" <soundneedle@ho tmail.com> wrote in message
      news:90fab935.0 312041735.14156 405@posting.goo gle.com...[color=blue]
      > I've read several prior posts in this group about using nz() to
      > convert null values to zero; however, I'm not sure how/where to
      > implement this function in my crosstab query.[/color]


      Most likely in the TRANSFORM line:

      TRANSFORM Avg(nz(qryPromo _NetCM.curNetCM )) AS AvgOfcurNetCM

      I haven't tried nz() in a crosstab, but it may work. If not, I believe this
      will:

      TRANSFORM iif(isnull(qryP romo_NetCM.curN etCM),0,
      avg(qryPromo_Ne tCM.curNetCM)) AS AvgOfcurNetCM



      [color=blue]
      > The crosstab query (qryPromoFilm_N etCM_Crosstab) uses another query
      > (qryPromo_NetCM ) as its source. The crosstab is used to show revenue
      > spread out through the twelve months. I need the months with null
      > values to have a "0" value.
      >
      > This is the SQL for the crosstab query (qryPromoFilm_N etCM_Crosstab):
      > TRANSFORM Avg(qryPromo_Ne tCM.curNetCM) AS AvgOfcurNetCM
      > SELECT qryPromo_NetCM. strPromoTitle, qryPromo_NetCM. strTitle
      > FROM qryPromo_NetCM
      > GROUP BY qryPromo_NetCM. strPromoTitle, qryPromo_NetCM. strTitle
      > PIVOT qryPromo_NetCM. lngFiscalMonthI D In (1,2,3,4,5,6,7, 8,9,10,11,12);
      >
      > This is the SQL for the crosstab query's source (qryPromo_NetCM ):
      > SELECT tblPromos.strPr omoTitle, tblFilms.strTit le,
      > tblPromo_NetCM. lngFiscalYear, tblFiscalMonths .strFiscalMonth ,
      > tblPromo_NetCM. curNetCM, tblPromo_NetCM. lngFiscalMonthI D,
      > tblPromo_Films. lngPromoFilmID
      > FROM tblPromos INNER JOIN ((tblFilms INNER JOIN tblPromo_Films ON
      > tblFilms.lngFil mID = tblPromo_Films. lngFilmID) INNER JOIN
      > (tblFiscalMonth s INNER JOIN tblPromo_NetCM ON
      > tblFiscalMonths .lngFiscalMonth ID = tblPromo_NetCM. lngFiscalMonthI D) ON
      > tblPromo_Films. lngPromoFilmID = tblPromo_NetCM. lngPromoFilmID) ON
      > tblPromos.lngPr omoID = tblPromo_Films. lngPromoID
      > ORDER BY tblPromos.strPr omoTitle, tblFilms.strTit le,
      > tblPromo_NetCM. lngFiscalYear, tblPromo_NetCM. lngFiscalMonthI D;
      >
      >
      > Any suggestions on where/how to implement the nz() function?
      >
      > TIA,
      > John[/color]


        Comment

        • Rick Brandt
          #3
          Re: Null values and NZ() in crosstab query

          "John" <soundneedle@ho tmail.com> wrote in message
          news:90fab935.0 312041735.14156 405@posting.goo gle.com...[color=blue]
          > I've read several prior posts in this group about using nz() to
          > convert null values to zero; however, I'm not sure how/where to
          > implement this function in my crosstab query.[/color]

          I'm not real sure what it would look like in the SQL, but essentially if
          you look at the Crosstab in the design grid the column being aggregated
          will typically have "Sum" or "Count" in the Total Row and the name of the
          field being aggregated in the Field row. You change the Total row to
          "Expression " and then change the Field row to "Nz(Sum(FieldNa me),0)" or
          Nz(Count(FieldN ame),0). Then you get zeros in the output instead of Nulls.
          I usually throw a "+0" onto end of the expression to force a numeric
          output. Otherwise the Nz() will change the output to a string.


          --
          I don't check the Email account attached
          to this message. Send instead to...
          RBrandt at Hunter dot com


            Comment

            • John
              #4
              Re: Null values and NZ() in crosstab query

              Rick, your suggestion worked just fine...thanks a lot for your help!

              -John

              "Rick Brandt" <rickbrandt2@ho tmail.com> wrote in message news:<bqq1q9$25 fa6q$1@ID-98015.news.uni-berlin.de>...[color=blue]
              > "John" <soundneedle@ho tmail.com> wrote in message
              > news:90fab935.0 312041735.14156 405@posting.goo gle.com...[color=green]
              > > I've read several prior posts in this group about using nz() to
              > > convert null values to zero; however, I'm not sure how/where to
              > > implement this function in my crosstab query.[/color]
              >
              > I'm not real sure what it would look like in the SQL, but essentially if
              > you look at the Crosstab in the design grid the column being aggregated
              > will typically have "Sum" or "Count" in the Total Row and the name of the
              > field being aggregated in the Field row. You change the Total row to
              > "Expression " and then change the Field row to "Nz(Sum(FieldNa me),0)" or
              > Nz(Count(FieldN ame),0). Then you get zeros in the output instead of Nulls.
              > I usually throw a "+0" onto end of the expression to force a numeric
              > output. Otherwise the Nz() will change the output to a string.[/color]

                Comment

                Previous template Next
                Working...