I hv table and sample data as below

Mth Prod Amt
Jan A 100
Jan B 150
Feb A 200
Feb B 100

When i do a report i've sorted & grouped based on 1) prod and 2)mth where i
create group header. My report will look like this

Prod A Jan 100
Feb 200

Prod B Jan 150
Feb 100

Q..How do i calculate variance for Feb & Jan by product and probably my
report will look like this


Prod A Jan 100
Feb 200
Var 100

Prod B Jan 150
Feb 100
Var -50

Appreciate your help.

hi,

zyus wrote:
> Q..How do i calculate variance for Feb & Jan by product and probably my
> report will look like this
Create a grouped query and use the Var() aggregation, something like

SELECT
[Product],
[Month],
Var([Price])
FROM
[yourTable]
GROUP BY
[Product],
[Month]



mfG
--> stefan <--

I tried with this SQL but returned 0 for the variance

SELECT [Tbl-VAR].Product, [Tbl-VAR].Mth, Var([Tbl-VAR].Amt) AS VarOfAmt
FROM [Tbl-VAR]
GROUP BY [Tbl-VAR].Product, [Tbl-VAR].Mth;

My sample table (Tbl-VAR) are as follow :

Product Mth Amt
P Jan 100
K Jan 500
P Feb 250
K Feb 200


TQ

"Stefan Hoffmann" wrote:

> hi,
>
> zyus wrote:
> > Q..How do i calculate variance for Feb & Jan by product and probably my
> > report will look like this
> Create a grouped query and use the Var() aggregation, something like
>
> SELECT
> [Product],
> [Month],
> Var([Price])
> FROM
> [yourTable]
> GROUP BY
> [Product],
> [Month]
>
>
>
> mfG
> --> stefan <--
>

hi,

zyus wrote:
> I tried with this SQL but returned 0 for the variance
>
> SELECT [Tbl-VAR].Product, [Tbl-VAR].Mth, Var([Tbl-VAR].Amt) AS VarOfAmt
> FROM [Tbl-VAR]
> GROUP BY [Tbl-VAR].Product, [Tbl-VAR].Mth;
>
> Product Mth Amt
> P Jan 100
> K Jan 500
> P Feb 250
> K Feb 200
The Variance can only be calculated if there is more than one value per
aggregation, e.g. with our data you can only the variance per year
(drop the group by month.



mfG
--> stefan <--

On Mon, 12 Nov 2007 14:06:12 +0100, Stefan Hoffmann
<(E-Mail Removed)> wrote:

>hi,
>
>zyus wrote:
>> I tried with this SQL but returned 0 for the variance
>>
>> SELECT [Tbl-VAR].Product, [Tbl-VAR].Mth, Var([Tbl-VAR].Amt) AS VarOfAmt
>> FROM [Tbl-VAR]
>> GROUP BY [Tbl-VAR].Product, [Tbl-VAR].Mth;
>>
>> Product Mth Amt
>> P Jan 100
>> K Jan 500
>> P Feb 250
>> K Feb 200
>The Variance can only be calculated if there is more than one value per
> aggregation, e.g. with our data you can only the variance per year
>(drop the group by month.
>
>
>
>mfG
>--> stefan <--

I think Zyus is using "variance" to just mean the change from month to month,
not the statistical measure.

John W. Vinson [MVP]