PC Review


Reply
Thread Tools Rate Thread

Sum Calculated Field in Crosstab Query

 
 
=?Utf-8?B?SmVyaQ==?=
Guest
Posts: n/a
 
      23rd Jul 2007
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
 
Reply With Quote
 
 
 
 
Gary Walter
Guest
Posts: n/a
 
      24th Jul 2007

"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


 
Reply With Quote
 
 
 
 
Gary Walter
Guest
Posts: n/a
 
      24th Jul 2007
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
>



 
Reply With Quote
 
=?Utf-8?B?SmVyaQ==?=
Guest
Posts: n/a
 
      24th Jul 2007
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
> >

>
>
>

 
Reply With Quote
 
=?Utf-8?B?SmVyaQ==?=
Guest
Posts: n/a
 
      25th Jul 2007
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
> >

>
>
>

 
Reply With Quote
 
 
 
Reply

Thread Tools
Rate This Thread
Rate This Thread:

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are Off


Similar Threads
Thread Thread Starter Forum Replies Last Post
Crosstab query is no longer crosstab when I include field (in repo Ben8765 Microsoft Access Reports 1 15th Oct 2009 10:01 PM
how to create a calculated field from another calculated field? =?Utf-8?B?RWxkb24=?= Microsoft Excel Misc 0 9th Jan 2006 04:40 PM
Calculated field identifiers used in another calculated field =?Utf-8?B?QWNhbnRob2Nhbmlz?= Microsoft Access Queries 3 23rd Sep 2005 05:57 AM
Criteria on field calculated form another calculated field Max Moor Microsoft Access Queries 1 22nd Dec 2004 09:19 AM
Re: Calculated field that uses another calculated field - possible? Lynn Trapp Microsoft Access Queries 0 20th Apr 2004 06:49 PM


Features
 

Advertising
 

Newsgroups
 


All times are GMT +1. The time now is 12:15 AM.