Calculate average in a query

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

i have a query like this

idno haemoglobin

1 10.2
2 14.6
3 10.2
4 18.0 and so on..

i want to find out min, max, mean and median of haemoglobin values using
query design window only.. is it also possible to count the number of
instances where the same value has occured ..eg in the above example- 10.2
has occured twice..
haemoglobin field is a number..

thanks
 
Hi -

Here's an example, based on Northwind's Orders table, that will return count,
min, max, mean. As you know, Access has no Median function so you have to
roll your own. Function Medianf(), shown below will do that for you.

SELECT
Count(Orders.Freight) AS CountOfFreight
, Min(Orders.Freight) AS MinOfFreight
, Max(Orders.Freight) AS MaxOfFreight
, Avg(Orders.Freight) AS MeanOfFreight
, Medianf("Orders","Freight") AS MedianOfFreight
FROM
Orders;

Function MedianF(pTable As String, pfield As String) As Single
'*******************************************
'Purpose: Return median value from a recordset
'Coded by: raskew
'Inputs: ? medianF("Orders", "Freight") <enter.
'Output: 41.36 (may vary according to how much
' you've fiddled with this table).
'*******************************************

Dim rs As Recordset
Dim strSQL As String
Dim n As Integer
Dim sglHold As Single

strSQL = "SELECT " & pfield & " from " & pTable & " WHERE " & pfield &
">0 Order by " & pfield & ";"
Set rs = CurrentDb.OpenRecordset(strSQL)
rs.MoveLast
n = rs.RecordCount
rs.Move -Int(n / 2)

If n Mod 2 = 1 Then 'odd number of elements
MedianF = rs(pfield)
Else 'even number of elements
sglHold = rs(pfield)
rs.MoveNext
sglHold = sglHold + rs(pfield)
MedianF = sglHold / 2
End If
rs.Close
End Function

is it also possible to count the number of
instances where the same value has occured ..eg in the above example- 10.2
has occured twice haemoglobin field is a number..
Click to expand...

Short answer: No! That's going to have to be a separate query.

HTH - Bob
 
Many thanks
is there no easy way? to average values in one field vvertically and
horizontally across ?
 
Thanks for ur patience
here is my problem in full!

this is how query design view looks ( without the gridlines!)

IDNO haemoglobin

1 10
2 20
3 60

haemoglobin is a number field.

i want to find the averagre of 10,20,30- which is 30 in this example.

i tried to add a third column in query design view- again haemoglobin- then
used avg function in row named total. the answer i get is

idno haem haem

1 10 10
2 20 20
3 60 60

so what i am doing is finding the average horizontally and not vertically
for the second column.. is there any way to do this without leaving the query
design view, if not, without any complicated programming?

thanks once again
--
sb


raskew via AccessMonster.com said:
Please provide an example of what you're after.

Bob
Many thanks
is there no easy way? to average values in one field vvertically and
horizontally across ?
Hi -
Click to expand...
[quoted text clipped - 67 lines]
thanks
Click to expand...
Click to expand...
Click to expand...
 
Thanks for ur patience
here is my problem in full!

this is how query design view looks ( without the gridlines!)

IDNO haemoglobin

1 10
2 20
3 60

haemoglobin is a number field.

i want to find the averagre of 10,20,30- which is 30 in this example.

i tried to add a third column in query design view- again haemoglobin- then
used avg function in row named total. the answer i get is

idno haem haem

1 10 10
2 20 20
3 60 60

so what i am doing is finding the average horizontally and not vertically
for the second column.. is there any way to do this without leaving the query
design view, if not, without any complicated programming?

thanks once again
Click to expand...
Maybe something like this:
SELECT Table1.idno, Table1.Haemoglobin, (Select Avg
(Haemoglobin) FROM Table1 as a Where a.idno <= Table1.idno) AS
Expr1
FROM Table1;
 
i want to find the averagre of 10,20,30- which is 30 in this example.
Better check your math. 10+20+30 is 60 and 60 / 3 = 20

Tom Lake
 
Thanks to both of u.

i was looking for a simpler solution ( which probably does not exists!)
thanks once again
 
Sudhir -

What's your contribution to this whole thing? You/ve been provided with
Count, Max, Min, Mean, Median, etc.. Now's the time to stand up and show us
your expertise.

Bob

Thanks to both of u.

i was looking for a simpler solution ( which probably does not exists!)
thanks once again
Tom -
Click to expand...
[quoted text clipped - 7 lines]
Click to expand...
 
Back
Top