By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
425,939 Members | 611 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 425,939 IT Pros & Developers. It's quick & easy.

need some suggestion.. am a new user of access

P: n/a
i have got a table of fields ID, study group, gender and height in
meters, and have got 9.99 in the height field as don't know option. I
want to create a query that calculates mean standard deviation,
maximum and minimum for studygroup1 gender1, study group1
gender2,studygroup2 gender1, study group2 gender2, and i also want to
display the labels as above.

any suggestion on how i would go abt it.
Nov 12 '05 #1
Share this Question
Share on Google+
3 Replies


P: n/a
The best solution is to leave the cell blank if you don't know the value.

In database parlance, this is known as a Null. Access does its sums
correctly when calculating averages if you leave it Null.

More information about Nulls and how to work with them in article:
Nulls: Do I need them?
at:
http://allenbrowne.com/casu-11.html

--
Allen Browne - Microsoft MVP. Perth, Western Australia.
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

"goma" <na*********@hotmail.com> wrote in message
news:c9*************************@posting.google.co m...
i have got a table of fields ID, study group, gender and height in
meters, and have got 9.99 in the height field as don't know option. I
want to create a query that calculates mean standard deviation,
maximum and minimum for studygroup1 gender1, study group1
gender2,studygroup2 gender1, study group2 gender2, and i also want to
display the labels as above.

any suggestion on how i would go abt it.

Nov 12 '05 #2

P: n/a
na*********@hotmail.com (goma) wrote in message news:<c9*************************@posting.google.c om>...
i have got a table of fields ID, study group, gender and height in
meters, and have got 9.99 in the height field as don't know option. I
want to create a query that calculates mean standard deviation,
maximum and minimum for studygroup1 gender1, study group1
gender2,studygroup2 gender1, study group2 gender2, and i also want to
display the labels as above.

any suggestion on how i would go abt it.


I'm not sure about the standard deviation although I'm sure it's
possible but the Min and Max are easy through an aggregate query. When
you open a query in design view there is a E (Greek E for Sum) button,
when you click this you are able to the grouping etc... Check out the
help for aggregate querys it should be relatively helpful

Philippa
Nov 12 '05 #3

P: n/a
HB
goma wrote:
i have got a table of fields ID, study group, gender and height in
meters, and have got 9.99 in the height field as don't know option. I
want to create a query that calculates mean standard deviation,
maximum and minimum for studygroup1 gender1, study group1
gender2,studygroup2 gender1, study group2 gender2, and i also want to
display the labels as above.

any suggestion on how i would go abt it.

You work around statisticians, right? They just love sticking
those extra 9s in where they don't belong!
As Alan already said, it's better to use a null where you need a
"don't know" value, especially when you're dealing with floating
point data. But a query something like this may work for you as a
quick fix:

SELECT studygroup, gender,
min(iif([height]=9.99, null, [height])) AS min_height,
max(iif([height]=9.99, null, [height])) AS max_height,
avg(iif([height]=9.99, null, [height])) AS mean_height,
stdev(iif([height]=9.99, null, [height])) AS std_height
FROM yourtable
GROUP BY studygroup, gender;

Hope this helps
HB

Nov 12 '05 #4

This discussion thread is closed

Replies have been disabled for this discussion.