Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

Agregate function over many columns

Posted on 2009-02-18
4
Medium Priority
?
250 Views
Last Modified: 2012-06-21
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

0
Comment
Question by:mock5c
  • 2
4 Comments
 
LVL 38

Assisted Solution

by:puppydogbuddy
puppydogbuddy earned 600 total points
ID: 23677187
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
 
LVL 58

Accepted Solution

by:
harfang earned 1400 total points
ID: 23677347
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
 

Author Comment

by:mock5c
ID: 23677423
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
 
LVL 58

Expert Comment

by:harfang
ID: 23677475
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

Featured Post

What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

We live in a world of interfaces like the one in the title picture. VBA also allows to use interfaces which offers a lot of possibilities. This article describes how to use interfaces in VBA and how to work around their bugs.
Instead of error trapping or hard-coding for non-updateable fields when using QODBC, let VBA automatically disable them when forms open. This way, users can view but not change the data. Part 1 explained how to use schema tables to do this. Part 2 h…
Add bar graphs to Access queries using Unicode block characters. Graphs appear on every record in the color you want. Give life to numbers. Hopes this gives you ideas on visualizing your data in new ways ~ Create a calculated field in a query: …
With just a little bit of  SQL and VBA, many doors open to cool things like synchronize a list box to display data relevant to other information on a form.  If you have never written code or looked at an SQL statement before, no problem! ...  give i…

810 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question