Calculate average in a query

[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

 

[raskew via AccessMonster.com]

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..

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

HTH - Bob

 

[Guest]

Many thanks
is there no easy way? to average values in one field vvertically and
horizontally across ?

 

[raskew via AccessMonster.com]

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 -

[quoted text clipped - 67 lines]

 

[Guest]

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

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 -

[quoted text clipped - 67 lines]

thanks

 

[Michael Gramelspacher]

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

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;

 

[Tom Lake]

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

 

[raskew via AccessMonster.com]

Tom -

Nice catch.

Bob

Better check your math. 10+20+30 is 60 and 60 / 3 = 20

Tom Lake

 

[Guest]

Thanks to both of u.

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

 

[raskew via AccessMonster.com]

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 -

[quoted text clipped - 7 lines]