Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

  • Congratulations derfloh on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

Looking for calculation trick for 4 columns 1

Status
Not open for further replies.

SidCharming

Technical User
Jun 18, 2003
73
US
I have four columns, they contain sizes. I want to perform calculations of Average, Standard Deviation, Min, Max and count all three together.

example:

[tt][teal]
size1 size2 size3 size4
----- ----- ----- -----

1 4 2 3
7 12 3
10 2
2
7 3 2
9 2
6 3 2[/teal][/tt]

My intent is to have all information needed contained in the report query... not have an external query. Or better yet, can I do something with code for this?

Currently I perform the calculations on each column, but still have to export the columns to Excel and do the calculations long hand for all data in the four columns.


Sid from Minnesota
 
Hi

You mean just on the sum of the columns?, in which case what is wrong with a calculated column in the query so:

X:StandardDev([Col1]+[Col2]+[Col3]+[Col4])

using the correct function name for standard deviation whatever and the correct column names

Regards

Ken Reay
Freelance Solutions Developer
Boldon Information Systems Ltd
Website needs upgrading, but for now - UK
 
Your main issue is your table structure where you have sizes as field/column names. If you normalized your data, you could use the Standard Deviation in a totals query. More normalized data would also allow you to add more sizes without adding fields or controls.

Duane
MS Access MVP
Find out how to get great answers faq219-2884.
 
I agree with normalizing the data, but these are databases I have inherrited.

as for performing [tt]X:StandardDev([Col1]+[Col2]+[Col3]+[Col4])[/tt] wouldn't that statement only take care of the row of data? End result is a normalized field. That way I can give the correct St. dev, Avg, Min, Max...




Sid from Minnesota
 
I would use a union query to normalize the data.
SELECT FieldA, Size1 as SizeOf, 1 as Size
FROM tblA
UNION
SELECT FieldA, Size2, 2
FROM tblA
UNION
...

Then use the results of the union query in a totals query that calculates your values.



Duane
MS Access MVP
Find out how to get great answers faq219-2884.
 
Thanks Duane. That is the best answer that does what I want. However it does leave me with more than one query. For now it does the trick until I/we, db team, get a chance to design the next database for more normalized data.

Sid

Star for you!

Sid from Minnesota
 
Glad to hear you are finding a solution.

[Red]Duane from near Minnesota who was in Minnesota Monday night and will be back in Minnesota in the near future and still considers Minnesota home and cheers for the T-Wolves[/red]
 
Status
Not open for further replies.

Part and Inventory Search

Sponsor

Back
Top