Sum Calculated Field in Crosstab Query

G

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;
 
G

Gary Walter

Jeri said:
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;
Click to expand...
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
 
G

Gary Walter

what I should have typed:

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


Jeri said:
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;
Click to expand...
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
Click to expand...
 
G

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 said:
what I should have typed:

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


Jeri said:
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;
Click to expand...
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
Click to expand...
Click to expand...
 
G

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 said:
what I should have typed:

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


Jeri said:
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;
Click to expand...
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
Click to expand...
Click to expand...
 

Ask a Question

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

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Crosstab query error 1
Duplicate values in Crosstab 2
insert a crosstab in to temp table query 2
Include empty values in crosstab query 5
AVG crosstab query with one field containing text 7
Calculated Field in Crosstab query 1
Odd Parameter request in Crosstab 4
Multiple expressions (Sum, Count, Average) of same value in crosstab query 3

Top