Median calculation

G

Guest

I'm using the following function which I found on another site.

Public Function DMedian(FieldName As String, _
TableName As String, _
Optional Criteria As Variant) As Double
On Error GoTo Err_DMedian
'Returns the median of a given field in a given table.
'Returns -1 if no recordset is created

Dim db As DAO.Database
Dim rs As DAO.Recordset
Dim strSQL As String
Dim RowCount As Long
Dim LowMedian As Double, HighMedian As Double

'Open a recordset on the table.
Set db = CurrentDb
strSQL = "SELECT " & FieldName & " FROM " & TableName
If Not IsMissing(Criteria) Then
strSQL = strSQL & " WHERE " & Criteria & " ORDER BY " & FieldName
Else
strSQL = strSQL & " ORDER BY " & FieldName
End If
' Debug.Print strSQL
Set rs = db.OpenRecordset(strSQL, dbOpenDynaset)

'Find the number of rows in the table.
rs.MoveLast
RowCount = rs.RecordCount
rs.MoveFirst
If RowCount Mod 2 = 0 Then
'There is an even number of records. Determine the low and high
'values in the middle and average them.
rs.Move Int(RowCount / 2) - 1
LowMedian = rs(FieldName)
rs.Move 1
HighMedian = rs(FieldName)
DMedian = (LowMedian + HighMedian) / 2
Else
'There is an odd number of records. Return the value exactly in
'the middle.
rs.Move Int(RowCount / 2)
DMedian = rs(FieldName)
End If

Exit_DMedian:
Exit Function

Err_DMedian:
If Err.Number = 3075 Then
DMedian = 0
Resume Exit_DMedian
ElseIf Err.Number = 3021 Then
'EOF or BOF ie no recordset created
DMedian = -1
Resume Exit_DMedian
Else
MsgBox Err.Description
Resume Exit_DMedian
End If
End Function

The problem I have is that I need to add another set of criteria to group
by. In addition to grouping by NU_USER_ID, I also need to group by Week
(which is on another table called Periods). I'm using this expression:
MedianYTD:
Dmedian("Time","TimeElapsed_byQuote","[TimeElapsed_byQuote]![NU_USER_ID] = '"
& [TimeElapsed_byQuote]![NU_USER_ID] & "' "). But How do I add the Week
criteria?
I need the query to return something like this:

User1 week1 5.5
User1 week2 6.3
User2 week1 5.7
User2 week2 7.1

How do I do this?
 
D

Douglas J. Steele

Already answered in another newsgroup to which you posted the same question.

If you feel you need to post to more than one group (HINT: it's seldom
necessary), please have the courtesy to cross-post (send the one message to
all groups at once), rather than multi-post (send individual messages to
each group). In this way, all responses to your post will be available
together, regardless of what group the responder was in, and the rest of us
won't have to read your post multiple times. (It also uses fewer server
resources)

If you're using Microsoft's web interface to post, you should see an
"Advanced Options" link at the bottom of the page. You can type the names of
the various groups into the Newsgroup box, separating each newsgroup name
with a semicolon.


--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)


ty said:
I'm using the following function which I found on another site.

Public Function DMedian(FieldName As String, _
TableName As String, _
Optional Criteria As Variant) As Double
On Error GoTo Err_DMedian
'Returns the median of a given field in a given table.
'Returns -1 if no recordset is created

Dim db As DAO.Database
Dim rs As DAO.Recordset
Dim strSQL As String
Dim RowCount As Long
Dim LowMedian As Double, HighMedian As Double

'Open a recordset on the table.
Set db = CurrentDb
strSQL = "SELECT " & FieldName & " FROM " & TableName
If Not IsMissing(Criteria) Then
strSQL = strSQL & " WHERE " & Criteria & " ORDER BY " & FieldName
Else
strSQL = strSQL & " ORDER BY " & FieldName
End If
' Debug.Print strSQL
Set rs = db.OpenRecordset(strSQL, dbOpenDynaset)

'Find the number of rows in the table.
rs.MoveLast
RowCount = rs.RecordCount
rs.MoveFirst
If RowCount Mod 2 = 0 Then
'There is an even number of records. Determine the low and high
'values in the middle and average them.
rs.Move Int(RowCount / 2) - 1
LowMedian = rs(FieldName)
rs.Move 1
HighMedian = rs(FieldName)
DMedian = (LowMedian + HighMedian) / 2
Else
'There is an odd number of records. Return the value exactly in
'the middle.
rs.Move Int(RowCount / 2)
DMedian = rs(FieldName)
End If

Exit_DMedian:
Exit Function

Err_DMedian:
If Err.Number = 3075 Then
DMedian = 0
Resume Exit_DMedian
ElseIf Err.Number = 3021 Then
'EOF or BOF ie no recordset created
DMedian = -1
Resume Exit_DMedian
Else
MsgBox Err.Description
Resume Exit_DMedian
End If
End Function

The problem I have is that I need to add another set of criteria to group
by. In addition to grouping by NU_USER_ID, I also need to group by Week
(which is on another table called Periods). I'm using this expression:
MedianYTD:
Dmedian("Time","TimeElapsed_byQuote","[TimeElapsed_byQuote]![NU_USER_ID] =
'"
& [TimeElapsed_byQuote]![NU_USER_ID] & "' "). But How do I add the Week
criteria?
I need the query to return something like this:

User1 week1 5.5
User1 week2 6.3
User2 week1 5.7
User2 week2 7.1

How do I do this?
Click to expand...
 
G

Guest

My apologies. I didn't know how to cross-post. I'll do it next time.

Douglas J. Steele said:
Already answered in another newsgroup to which you posted the same question.

If you feel you need to post to more than one group (HINT: it's seldom
necessary), please have the courtesy to cross-post (send the one message to
all groups at once), rather than multi-post (send individual messages to
each group). In this way, all responses to your post will be available
together, regardless of what group the responder was in, and the rest of us
won't have to read your post multiple times. (It also uses fewer server
resources)

If you're using Microsoft's web interface to post, you should see an
"Advanced Options" link at the bottom of the page. You can type the names of
the various groups into the Newsgroup box, separating each newsgroup name
with a semicolon.


--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)


ty said:
I'm using the following function which I found on another site.

Public Function DMedian(FieldName As String, _
TableName As String, _
Optional Criteria As Variant) As Double
On Error GoTo Err_DMedian
'Returns the median of a given field in a given table.
'Returns -1 if no recordset is created

Dim db As DAO.Database
Dim rs As DAO.Recordset
Dim strSQL As String
Dim RowCount As Long
Dim LowMedian As Double, HighMedian As Double

'Open a recordset on the table.
Set db = CurrentDb
strSQL = "SELECT " & FieldName & " FROM " & TableName
If Not IsMissing(Criteria) Then
strSQL = strSQL & " WHERE " & Criteria & " ORDER BY " & FieldName
Else
strSQL = strSQL & " ORDER BY " & FieldName
End If
' Debug.Print strSQL
Set rs = db.OpenRecordset(strSQL, dbOpenDynaset)

'Find the number of rows in the table.
rs.MoveLast
RowCount = rs.RecordCount
rs.MoveFirst
If RowCount Mod 2 = 0 Then
'There is an even number of records. Determine the low and high
'values in the middle and average them.
rs.Move Int(RowCount / 2) - 1
LowMedian = rs(FieldName)
rs.Move 1
HighMedian = rs(FieldName)
DMedian = (LowMedian + HighMedian) / 2
Else
'There is an odd number of records. Return the value exactly in
'the middle.
rs.Move Int(RowCount / 2)
DMedian = rs(FieldName)
End If

Exit_DMedian:
Exit Function

Err_DMedian:
If Err.Number = 3075 Then
DMedian = 0
Resume Exit_DMedian
ElseIf Err.Number = 3021 Then
'EOF or BOF ie no recordset created
DMedian = -1
Resume Exit_DMedian
Else
MsgBox Err.Description
Resume Exit_DMedian
End If
End Function

The problem I have is that I need to add another set of criteria to group
by. In addition to grouping by NU_USER_ID, I also need to group by Week
(which is on another table called Periods). I'm using this expression:
MedianYTD:
Dmedian("Time","TimeElapsed_byQuote","[TimeElapsed_byQuote]![NU_USER_ID] =
'"
& [TimeElapsed_byQuote]![NU_USER_ID] & "' "). But How do I add the Week
criteria?
I need the query to return something like this:

User1 week1 5.5
User1 week2 6.3
User2 week1 5.7
User2 week2 7.1

How do I do this?
Click to expand...
Click to expand...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Problem with Median Calculation / VBA Script 2
Median 6
median function 8
Calculat Median for each data group? 3
Update query issue 0
Median Function Problem w/ select statement 2
Median Calculation, Dates, & Group By 1
Syntax error (missing operator) 4

Top