STDEV on multiple column from same line 3

[haerion]

Hi all,

Was wondering if it is possible to do a STDEV on 12 column of the same line instead of doing a group one of the same column. I have 12 column, each of them being a month, from Jan to Dec, and I need to make a STDEV out of this.

Thanks for your help!

Haerion

 

[PHV]

Have a look here:

http://r937.com/relational.html

then do a search in this forum for normalization union query.

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[haerion]

Hi PHV, thanks for your answer, unfortunately I don't think i will be able to solve it like that, unless I'm missing something as to how to create union query with transforming the column into line.

The way my table is, made it impossible to change it, since it come from a odbc file.

My table is as follow

Item# jan feb mar apr may etc
UI778 2 5 7 5 4 ...

I need to have in a query:

Item# Average StandardDeviation
UI778 4.6 1.82

I think it would be possible if i could change my table to become like that:

Item# Qty
UI778 2
UI778 5
UI778 7
UI778 5
UI778 4

Have any idea how I could create a module or something like that that would do the job?

 

[haerion]

ok, I tried with a union query to normalize it, but when a qty is the same for 2 month or more, than only one of them is reported back, so if my table is like that:

Item# jan feb mar apr may etc
UI778 2 5 7 5 4 ...

it will give me :
Item# Qty
UI778 2
UI778 5
UI778 7
UI778 4

instead of that:
Item# Qty
UI778 2
UI778 5
UI778 7
UI778 5 <---- this one dissapear
UI778 4


Here is my query:

SELECT SALE24V1.ST, SALE24V1.[ITEM #], SALE24V1.[jan] AS Qty
FROM SALE24V1;
union
SELECT SALE24V1.ST, SALE24V1.[ITEM #], SALE24V1.[feb] AS Qty
FROM SALE24V1;
union
SELECT SALE24V1.ST, SALE24V1.[ITEM #], SALE24V1.[mar] AS Qty
FROM SALE24V1;
union
SELECT SALE24V1.ST, SALE24V1.[ITEM #], SALE24V1.[apr] AS Qty
FROM SALE24V1;
union
SELECT SALE24V1.ST, SALE24V1.[ITEM #], SALE24V1.[may] AS Qty
FROM SALE24V1;

 

[dhookom]

Try use UNION ALL and only one semi-colon at the end.

SELECT ST, [ITEM #], [jan] AS Qty, 1 As Mth
FROM SALE24V1
UNION ALL
SELECT ST, [ITEM #], [feb],2
FROM SALE24V1
UNION ALL
SELECT ST, [ITEM #], [mar],3
FROM SALE24V1
UNION ALL
SELECT ST, [ITEM #], [apr],4
FROM SALE24V1
UNION ALL
SELECT ST, [ITEM #], [may],5
FROM SALE24V1;

Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]

 

[haerion]

Hi Duane,

I tried the code you gave me and it is working perfectly, except that I have a lot of data

I had 52,000 lines before, and since there is 24 month of data to include, my number of line have gone up to 1,248,000 lines héhé.

Is there another way to come up to the same result, but faster? It take like 30 sec to come up with the union query.

Anyway you still got a start for this one, will help me in the future, didn't know about the union all

 

[dhookom]

The suggestion for the correct solution actually can originally from PH.

I would make sure any field you use for sorting or filtering is indexed. Do you always need to work with 24 months of data?

Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]

 

[haerion]

Yeah, I always need to work with all the 24 months, I tried to do a union from a query, but seem the union work only with table

 

[dhookom]

You should be able to create a union query based on tables or queries.

Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]

 

[haerion]

Weird then, when I replaced the SALE24V1 by SALE24V (my query), it asked me for a query1.ST, so I tried to replace the ST by SALE24V.ST and it still asked me for the same thing

 

[dhookom]

Share your SQL view.

Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]

 

[haerion]

Here is the sql for the union:

SELECT ST, [ITEM #],  1 As Mth,[STK UNIT SLS] AS Qty
FROM SALE24V1
UNION ALL
SELECT ST, [ITEM #],2,[NOR2M]
FROM SALE24V1
UNION ALL
SELECT ST, [ITEM #],3,[NOR3M]
FROM SALE24V1
UNION ALL
SELECT ST, [ITEM #],4,[NOR4M]
FROM SALE24V1
UNION ALL
SELECT ST, [ITEM #],5,[NOR5M]
FROM SALE24V1
UNION ALL
SELECT ST, [ITEM #],6,[NOR6M]
FROM SALE24V1
UNION ALL
SELECT ST, [ITEM #],7,[NOR7M]
FROM SALE24V1
UNION ALL
SELECT ST, [ITEM #],8,[NOR8M]
FROM SALE24V1
UNION ALL
SELECT ST, [ITEM #],9,[NOR9M]
FROM SALE24V1
UNION ALL
SELECT ST, [ITEM #],10,[NOR10M]
FROM SALE24V1
UNION ALL
SELECT ST, [ITEM #],11,[NOR11M]
FROM SALE24V1
UNION ALL
SELECT ST, [ITEM #],12,[NOR12M]
FROM SALE24V1
UNION ALL
SELECT ST, [ITEM #],13,[NOR13M]
FROM SALE24V2
UNION ALL
SELECT ST, [ITEM #],14,[NOR14M]
FROM SALE24V2
UNION ALL
SELECT ST, [ITEM #],15,[NOR15M]
FROM SALE24V2
UNION ALL
SELECT ST, [ITEM #],16,[NOR16M]
FROM SALE24V2
UNION ALL
SELECT ST, [ITEM #],17,[NOR17M]
FROM SALE24V2
UNION ALL
SELECT ST, [ITEM #],18,[NOR18M]
FROM SALE24V2
UNION ALL
SELECT ST, [ITEM #],19,[NOR19M]
FROM SALE24V2
UNION ALL
SELECT ST, [ITEM #],20,[NOR20M]
FROM SALE24V2
UNION ALL
SELECT ST, [ITEM #],21,[NOR21M]
FROM SALE24V2
UNION ALL
SELECT ST, [ITEM #],22,[NOR22M]
FROM SALE24V2
UNION ALL
SELECT ST, [ITEM #],23,[NOR23M]
FROM SALE24V2
UNION ALL SELECT ST, [ITEM #],24,[NOR24M]
FROM SALE24V2;

the sql for my query I would like to do the union with:

SELECT SALE24V1.ST AS ST, SALE24V1.[ITEM #] AS [ITEM #], SALE24V1.[STK UNIT SLS], SALE24V1.NOR2M, SALE24V1.NOR3M, SALE24V1.NOR4M, SALE24V1.NOR5M, SALE24V1.NOR6M, SALE24V1.NOR7M, SALE24V1.NOR8M, SALE24V1.NOR9M, SALE24V1.NOR10M, SALE24V1.NOR11M, SALE24V1.NOR12M, SALE24V2.NOR13M, SALE24V2.NOR14M, SALE24V2.NOR15M, SALE24V2.NOR16M, SALE24V2.NOR17M, SALE24V2.NOR18M, SALE24V2.NOR19M, SALE24V2.NOR20M, SALE24V2.NOR21M, SALE24V2.NOR22M, SALE24V2.NOR23M, SALE24V2.NOR24M, [YTD]+[LYTD] AS TOTAL
FROM SALE24V1 INNER JOIN SALE24V2 ON SALE24V1.STITEM = SALE24V2.STITEM
WHERE ((([YTD]+[LYTD])>0));

In the first sql I replaced the SALE24V1 and V2 by SALE24V that is the name of my query, but it doesn't seem to work any idea?

Thanks for your help and patience Duane

 

[dhookom]

I'm not sure if this will make a difference but I wouldn't alias a column with its own name:

SELECT SALE24V1.ST, SALE24V1.[ITEM #], 
SALE24V1.[STK UNIT SLS], SALE24V1.NOR2M, SALE24V1.NOR3M, SALE24V1.NOR4M, SALE24V1.NOR5M, SALE24V1.NOR6M, SALE24V1.NOR7M, SALE24V1.NOR8M, SALE24V1.NOR9M, SALE24V1.NOR10M, SALE24V1.NOR11M, SALE24V1.NOR12M, SALE24V2.NOR13M, SALE24V2.NOR14M, SALE24V2.NOR15M, SALE24V2.NOR16M, SALE24V2.NOR17M, SALE24V2.NOR18M, SALE24V2.NOR19M, SALE24V2.NOR20M, SALE24V2.NOR21M, SALE24V2.NOR22M, SALE24V2.NOR23M, SALE24V2.NOR24M, [YTD]+[LYTD] AS TOTAL
FROM SALE24V1 INNER JOIN SALE24V2 ON SALE24V1.STITEM = SALE24V2.STITEM
WHERE ((([YTD]+[LYTD])>0));

Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]

 

[haerion]

Yeah it doesn't change a thing, since I change it with is own name since the other didn't worked either

 

[dhookom]

I guess I'm not understanding which query works and which one doesn't. Your union query seems to be pulling from multiple query/tables.

Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]

 

[MichaelRed]

there are several instances of Array (row) aggregates in these fora. The basic concept is to use a procedure with a ParamArray as an input argument. Call the procedure with the fields on interest for your aggregate function and do the aggregate function from the procedure, returning the results set. There is (also) somewhere within these fora a procedure for SDEV, base on the Excel model as documented by MS in an older version of the Excel functions reference. You should be able to find the aggreagate functions easily by searching these fora for the key word "ParamArray". The STDEV function may be a bit harder, but it should be smoething like basSTDEV ...

1.2 Mill 'rows' is going to take Ms. A. (aka Jet / Jet+) a bit more time than most are willing to devote to the exercise.




MichaelRed