Agregate function over many columns

I've provided part of a CSV file.  I would like a single query that can show me averages and stdev for PartNums for the length,weight, and height columns.

e.g.

avg(length),avg(weight),avg(height) of:
PartID=A,PartNum=P1;PartID=B,PartNum=P1;PartID=C,PartNum=P1
A,P2;B,P2;C,P2
A,P3;B,P3;C,P3
and so on ...

I have over 70 columns so eventually I'd like to apply this query for all columns.


PartID,PartNum,PartLength,PartWeight,PartHeight
A,P1,8.0353,0.551,210.0
A,P2,6.7334,0.478,179.5
A,P3,11.9139,0.922,125.1
A,P4,3.8513,0.291,99.9
A,P5,2.6203,0.199,80.7
B,P1,14.8999,1.239,35.6
B,P2,26.7577,3.513,69.8
B,P3,10.2892,1.000,112.0
B,P4,9.7443,0.872,103.8
B,P5,0.1506,0.013,30.5
C,P1,2.8813,0.178,35.6
C,P2,18.519,2.001,231.8
C,P3,7.0322,0.899,77.7
C,P4,5.5400,0.313,61.2
C,P5,3.0951,0.190,35.9

Open in new window

mock5cAsked:
Who is Participating?
 
harfangCommented:
I'm not sure which average you want: PartNums across PartIDs or PartIDs across PartNums... Since you have unique combinations, you clearly don't want one record for each. Have you tried a simple total query, something like:

SELECT
 PartNum,
 Avg(PartLength) AS AvgOfPartLength,
 StDev(PartLength) AS StDevOfPartLength,
 Avg(PartWeight) AS AvgOfPartWeight,
 StDev(PartWeight) AS StDevOfPartWeight,
 Avg(PartHeight) AS AvgOfPartHeight,
 StDev(PartHeight) AS StDevOfPartHeight
FROM YourCSV
GROUP BY PartNum;

(°v°)
0
 
puppydogbuddyCommented:
see this link:
           http://support.microsoft.com/kb/209839
Although Microsoft Access has several built-in functions that enable you to perform statistical analysis across records, it does not have a built-in function to perform statistical analysis across multiple columns within a single row. This article shows you how to create several sample user-defined functions that you can use to get row-level statistics in Access.
0
 
mock5cAuthor Commented:
That link seems useful but I don't need to compute statistical analysis over multiple columns.  I only need to compute on a per column basis.  So, for example, we can forget about my weight and height columns and just pretend there is only a length column.  Now, from the length column, I would like to know:

Average of
PartID=A,PartNum=P1;PartID=B,PartNum=P1;PartID=C,PartNum=P1
A,P2;B,P2;C,P2
A,P3;B,P3;C,P3
and so on ...

That is:
avg(8.0353,14.8999,2.8813)
avg(6.7334,26.7577,18.519)
avg(11.9139,10.2892,7.0322)
and so on ...


0
 
harfangCommented:
The query I showed does just that. Import or link your CSV, open the query assistant, choose "total query", group by PartNum, and apply the functions you need to each column (you can use the same column name several times to get average and standard deviation for example).

Good luck!
(°v°)
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.