Sum Calculated Field in Crosstab Query

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Jeri - 23 Jul 2007 21:48 GMT

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;

Signature

www.slothmovie.com

Reply to this Message

Gary Walter - 24 Jul 2007 16:05 GMT

>I have a crosstab query with the SQL below for flow measurements. I am
>trying
[quoted text clipped - 12 lines]
> 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

Reply to this Message

Gary Walter - 24 Jul 2007 16:50 GMT

what I should have typed:

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

"Gary Walter" wrote erroneously:

>>I have a crosstab query with the SQL below for flow measurements. I am
>>trying
[quoted text clipped - 34 lines]
>
> gary

Reply to this Message

Jeri - 24 Jul 2007 23:52 GMT

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

Signature

www.slothmovie.com

> what I should have typed:
>
[quoted text clipped - 40 lines]
> >
> > gary

Reply to this Message

Jeri - 25 Jul 2007 01:18 GMT

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])

Signature

www.slothmovie.com

> what I should have typed:
>
[quoted text clipped - 40 lines]
> >
> > gary

Reply to this Message