Sum Calculated Field in Crosstab Query

Discussion in 'Microsoft Access Queries' started by Guest, Jul 23, 2007.

  1. Guest

    Guest Guest

    I have a crosstab query with the SQL below for flow measurements. I am trying
    to place the sum of cell averages on a form. I've entered the expression
    =DSum("[CellAverage]","[qry_VolMeterCrosstab]") but get an incorrect result.
    Any suggestions? Thanks,

    PARAMETERS [Forms]![frm_Survey]![Survey_ID] Long;
    TRANSFORM Avg(qry_MeterMeasurements.LitersSecond) AS AvgOfLitersSecond
    SELECT qry_MeterMeasurements.CellNumber, qry_MeterMeasurements.Survey_ID,
    Avg(qry_MeterMeasurements.LitersSecond) AS CellAverage
    FROM qry_MeterMeasurements
    WHERE (((qry_MeterMeasurements.Survey_ID)=[Forms]![frm_Survey]![Survey_ID]))
    GROUP BY qry_MeterMeasurements.CellNumber, qry_MeterMeasurements.Survey_ID
    PIVOT qry_MeterMeasurements.MeasureNumber;

    --
    www.slothmovie.com
     
    Guest, Jul 23, 2007
    #1
    1. Advertisements

  2. Guest

    Gary Walter Guest

    "Jeri" wrote:
    >I have a crosstab query with the SQL below for flow measurements. I am
    >trying
    > to place the sum of cell averages on a form. I've entered the expression
    > =DSum("[CellAverage]","[qry_VolMeterCrosstab]") but get an incorrect
    > result.
    > Any suggestions? Thanks,
    >
    > PARAMETERS [Forms]![frm_Survey]![Survey_ID] Long;
    > TRANSFORM Avg(qry_MeterMeasurements.LitersSecond) AS AvgOfLitersSecond
    > SELECT qry_MeterMeasurements.CellNumber, qry_MeterMeasurements.Survey_ID,
    > Avg(qry_MeterMeasurements.LitersSecond) AS CellAverage
    > FROM qry_MeterMeasurements
    > WHERE
    > (((qry_MeterMeasurements.Survey_ID)=[Forms]![frm_Survey]![Survey_ID]))
    > GROUP BY qry_MeterMeasurements.CellNumber, qry_MeterMeasurements.Survey_ID
    > PIVOT qry_MeterMeasurements.MeasureNumber;
    >

    Hi Jeri,

    I apologise in advance but I have to ask why?

    What meaningful insight can one get from the
    sum of averages (maybe there is something I
    have not thought of before)?

    Is there a chance you were looking to later compute the
    *total avg for the survey_id*?

    =DAvg("[CellAverage]","[qry_VolMeterCrosstab]",
    "[Survey_ID] =" & [Forms]![frm_Survey]![Survey_ID])

    Apologies again, there may be something I just do
    not understand...

    good luck,

    gary
     
    Gary Walter, Jul 24, 2007
    #2
    1. Advertisements

  3. Guest

    Gary Walter Guest

    what I should have typed:

    =DAvg("[LitersSecond]","[qry_MeterMeasurements]",
    "[Survey_ID] =" & [Forms]![frm_Survey]![Survey_ID])


    "Gary Walter" wrote erroneously:
    > "Jeri" wrote:
    >>I have a crosstab query with the SQL below for flow measurements. I am
    >>trying
    >> to place the sum of cell averages on a form. I've entered the expression
    >> =DSum("[CellAverage]","[qry_VolMeterCrosstab]") but get an incorrect
    >> result.
    >> Any suggestions? Thanks,
    >>
    >> PARAMETERS [Forms]![frm_Survey]![Survey_ID] Long;
    >> TRANSFORM Avg(qry_MeterMeasurements.LitersSecond) AS AvgOfLitersSecond
    >> SELECT qry_MeterMeasurements.CellNumber, qry_MeterMeasurements.Survey_ID,
    >> Avg(qry_MeterMeasurements.LitersSecond) AS CellAverage
    >> FROM qry_MeterMeasurements
    >> WHERE
    >> (((qry_MeterMeasurements.Survey_ID)=[Forms]![frm_Survey]![Survey_ID]))
    >> GROUP BY qry_MeterMeasurements.CellNumber,
    >> qry_MeterMeasurements.Survey_ID
    >> PIVOT qry_MeterMeasurements.MeasureNumber;
    >>

    > Hi Jeri,
    >
    > I apologise in advance but I have to ask why?
    >
    > What meaningful insight can one get from the
    > sum of averages (maybe there is something I
    > have not thought of before)?
    >
    > Is there a chance you were looking to later compute the
    > *total avg for the survey_id*?
    >
    > =DAvg("[CellAverage]","[qry_VolMeterCrosstab]",
    > "[Survey_ID] =" & [Forms]![frm_Survey]![Survey_ID])
    >
    > Apologies again, there may be something I just do
    > not understand...
    >
    > good luck,
    >
    > gary
    >
     
    Gary Walter, Jul 24, 2007
    #3
  4. Guest

    Guest Guest

    I understand this is an unusual formula. When taking measurements of flow
    volume of a stream in cubic meters per second with a flow meter, you separate
    the channel into "cells." From each cell you take several measurements with
    the flow meter and multiply each by the width and depth of the cell. Then you
    take an average of those measurements to find the flow in each cell. The
    total flow is the sum of the values in the cells. The database is designed to
    inventory the condition of springs. Thanks so much.

    Jeri
    --
    www.slothmovie.com


    "Gary Walter" wrote:

    > what I should have typed:
    >
    > =DAvg("[LitersSecond]","[qry_MeterMeasurements]",
    > "[Survey_ID] =" & [Forms]![frm_Survey]![Survey_ID])
    >
    >
    > "Gary Walter" wrote erroneously:
    > > "Jeri" wrote:
    > >>I have a crosstab query with the SQL below for flow measurements. I am
    > >>trying
    > >> to place the sum of cell averages on a form. I've entered the expression
    > >> =DSum("[CellAverage]","[qry_VolMeterCrosstab]") but get an incorrect
    > >> result.
    > >> Any suggestions? Thanks,
    > >>
    > >> PARAMETERS [Forms]![frm_Survey]![Survey_ID] Long;
    > >> TRANSFORM Avg(qry_MeterMeasurements.LitersSecond) AS AvgOfLitersSecond
    > >> SELECT qry_MeterMeasurements.CellNumber, qry_MeterMeasurements.Survey_ID,
    > >> Avg(qry_MeterMeasurements.LitersSecond) AS CellAverage
    > >> FROM qry_MeterMeasurements
    > >> WHERE
    > >> (((qry_MeterMeasurements.Survey_ID)=[Forms]![frm_Survey]![Survey_ID]))
    > >> GROUP BY qry_MeterMeasurements.CellNumber,
    > >> qry_MeterMeasurements.Survey_ID
    > >> PIVOT qry_MeterMeasurements.MeasureNumber;
    > >>

    > > Hi Jeri,
    > >
    > > I apologise in advance but I have to ask why?
    > >
    > > What meaningful insight can one get from the
    > > sum of averages (maybe there is something I
    > > have not thought of before)?
    > >
    > > Is there a chance you were looking to later compute the
    > > *total avg for the survey_id*?
    > >
    > > =DAvg("[CellAverage]","[qry_VolMeterCrosstab]",
    > > "[Survey_ID] =" & [Forms]![frm_Survey]![Survey_ID])
    > >
    > > Apologies again, there may be something I just do
    > > not understand...
    > >
    > > good luck,
    > >
    > > gary
    > >

    >
    >
    >
     
    Guest, Jul 24, 2007
    #4
  5. Guest

    Guest Guest

    This got the correct result, the sum of average measurements for each cell,
    which equals the total flow of the sidestream. Thank you so much for your
    assistance.

    =DSum("[CellAverage]","[qry_VolMeterCrosstab]","[Survey_ID] =" &
    [Forms]![frm_Survey]![Survey_ID])
    --
    www.slothmovie.com


    "Gary Walter" wrote:

    > what I should have typed:
    >
    > =DAvg("[LitersSecond]","[qry_MeterMeasurements]",
    > "[Survey_ID] =" & [Forms]![frm_Survey]![Survey_ID])
    >
    >
    > "Gary Walter" wrote erroneously:
    > > "Jeri" wrote:
    > >>I have a crosstab query with the SQL below for flow measurements. I am
    > >>trying
    > >> to place the sum of cell averages on a form. I've entered the expression
    > >> =DSum("[CellAverage]","[qry_VolMeterCrosstab]") but get an incorrect
    > >> result.
    > >> Any suggestions? Thanks,
    > >>
    > >> PARAMETERS [Forms]![frm_Survey]![Survey_ID] Long;
    > >> TRANSFORM Avg(qry_MeterMeasurements.LitersSecond) AS AvgOfLitersSecond
    > >> SELECT qry_MeterMeasurements.CellNumber, qry_MeterMeasurements.Survey_ID,
    > >> Avg(qry_MeterMeasurements.LitersSecond) AS CellAverage
    > >> FROM qry_MeterMeasurements
    > >> WHERE
    > >> (((qry_MeterMeasurements.Survey_ID)=[Forms]![frm_Survey]![Survey_ID]))
    > >> GROUP BY qry_MeterMeasurements.CellNumber,
    > >> qry_MeterMeasurements.Survey_ID
    > >> PIVOT qry_MeterMeasurements.MeasureNumber;
    > >>

    > > Hi Jeri,
    > >
    > > I apologise in advance but I have to ask why?
    > >
    > > What meaningful insight can one get from the
    > > sum of averages (maybe there is something I
    > > have not thought of before)?
    > >
    > > Is there a chance you were looking to later compute the
    > > *total avg for the survey_id*?
    > >
    > > =DAvg("[CellAverage]","[qry_VolMeterCrosstab]",
    > > "[Survey_ID] =" & [Forms]![frm_Survey]![Survey_ID])
    > >
    > > Apologies again, there may be something I just do
    > > not understand...
    > >
    > > good luck,
    > >
    > > gary
    > >

    >
    >
    >
     
    Guest, Jul 25, 2007
    #5
    1. Advertisements

Want to reply to this thread or ask your own question?

It takes just 2 minutes to sign up (and it's free!). Just click the sign up button to choose a username and then you can ask your own questions on the forum.
Similar Threads
  1. Rebecca

    Add calculated field to Crosstab query ?

    Rebecca, Oct 7, 2003, in forum: Microsoft Access Queries
    Replies:
    1
    Views:
    1,020
    Cameo
    Oct 8, 2003
  2. Lynn Trapp
    Replies:
    0
    Views:
    255
    Lynn Trapp
    Apr 20, 2004
  3. Max Moor

    Criteria on field calculated form another calculated field

    Max Moor, Dec 22, 2004, in forum: Microsoft Access Queries
    Replies:
    1
    Views:
    405
    Allen Browne
    Dec 22, 2004
  4. Guest
    Replies:
    3
    Views:
    298
    Allen Browne
    Sep 23, 2005
  5. Paul
    Replies:
    3
    Views:
    438
    John Spencer
    Jan 11, 2007
Loading...

Share This Page