Calculating median in a group by query

[Matt]

Hi all,

I have a group by query with two fields p_code and cost_code that I need to
calculate the median on the weight. I'm running into trouble because the
table is about 100k records with 133 unique p_code and cost_code combinations.

With my current median function, it only calculates the weight of the whole
list and not by my group by combination (pcode and cost_code).

Is there a way to calculate the median using my group by set of data? If
not, I have to create 133 separate tables and calculate the median in each
table.

Here's the code I was using to calculate the median (taken from microsoft
knowledge base and only works for one set of data):

Option Compare Database

Option Explicit

Function Median(tName As String, fldName As String) As Single
Dim MedianDB As DAO.Database
Dim ssMedian As DAO.Recordset
Dim RCount As Integer, i As Integer, x As Double, y As Double, _
OffSet As Integer
Set MedianDB = CurrentDb()
Set ssMedian = MedianDB.OpenRecordset("SELECT [" & fldName & _
"] FROM [" & tName & "] WHERE [" & fldName & _
"] IS NOT NULL ORDER BY [" & fldName & "];")
'NOTE: To include nulls when calculating the median value, omit
'WHERE [" & fldName & "] IS NOT NULL from the example.
ssMedian.MoveLast
RCount% = ssMedian.RecordCount
x = RCount Mod 2
If x <> 0 Then
OffSet = ((RCount + 1) / 2) - 2
For i% = 0 To OffSet
ssMedian.MovePrevious
Next i
Median = ssMedian(fldName)
Else
OffSet = (RCount / 2) - 2
For i = 0 To OffSet
ssMedian.MovePrevious
Next i
x = ssMedian(fldName)
ssMedian.MovePrevious
y = ssMedian(fldName)
Median = (x + y) / 2
End If
If Not ssMedian Is Nothing Then
ssMedian.Close
Set ssMedian = Nothing
End If
Set MedianDB = Nothing
End Function

Thanks in advance for your help.

Best Regards,
Matt

 

[Jeff Boyce]

Matt

Why would you need "133 separate tables"? We're not there -- we don't know
what you're storing in your tables or how.

If you need to calculate medians, have you considered exporting data to
Excel and using Excel's Median() function?

Regards

Jeff Boyce
Microsoft Office/Access MVP

 

[Matt]

Jeff,

I'm trying to calculate the median based on the p_code and cost_code using
the weight for each part number. So, I need to calculate the median based on
each unique set. The total table is too big for excel that's why I cannot do
it in excel.

Example of the data

pcode cost code

01 A
05 B
07 C
10 D

Within each pcode and cost code combination, there are several hundred part
numbers with varying weights. I'm trying to calculate the median for each of
these combinations based on the weight.

Does that clarify what I'm trying to do?

Matt

Why would you need "133 separate tables"? We're not there -- we don't know
what you're storing in your tables or how.

If you need to calculate medians, have you considered exporting data to
Excel and using Excel's Median() function?

Regards

Jeff Boyce
Microsoft Office/Access MVP

Hi all,

I have a group by query with two fields p_code and cost_code that I need
to
calculate the median on the weight. I'm running into trouble because the
table is about 100k records with 133 unique p_code and cost_code
combinations.

With my current median function, it only calculates the weight of the
whole
list and not by my group by combination (pcode and cost_code).

Is there a way to calculate the median using my group by set of data? If
not, I have to create 133 separate tables and calculate the median in each
table.

Here's the code I was using to calculate the median (taken from microsoft
knowledge base and only works for one set of data):

Option Compare Database

Option Explicit

Function Median(tName As String, fldName As String) As Single
Dim MedianDB As DAO.Database
Dim ssMedian As DAO.Recordset
Dim RCount As Integer, i As Integer, x As Double, y As Double, _
OffSet As Integer
Set MedianDB = CurrentDb()
Set ssMedian = MedianDB.OpenRecordset("SELECT [" & fldName & _
"] FROM [" & tName & "] WHERE [" & fldName & _
"] IS NOT NULL ORDER BY [" & fldName & "];")
'NOTE: To include nulls when calculating the median value, omit
'WHERE [" & fldName & "] IS NOT NULL from the example.
ssMedian.MoveLast
RCount% = ssMedian.RecordCount
x = RCount Mod 2
If x <> 0 Then
OffSet = ((RCount + 1) / 2) - 2
For i% = 0 To OffSet
ssMedian.MovePrevious
Next i
Median = ssMedian(fldName)
Else
OffSet = (RCount / 2) - 2
For i = 0 To OffSet
ssMedian.MovePrevious
Next i
x = ssMedian(fldName)
ssMedian.MovePrevious
y = ssMedian(fldName)
Median = (x + y) / 2
End If
If Not ssMedian Is Nothing Then
ssMedian.Close
Set ssMedian = Nothing
End If
Set MedianDB = Nothing
End Function

Thanks in advance for your help.

Best Regards,
Matt

 

[Jeff Boyce]

Matt

Perhaps I'm missing something. I still don't understand why you have "133
tables".

Regards

Jeff Boyce
Microsoft Office/Access MVP

Jeff,

I'm trying to calculate the median based on the p_code and cost_code using
the weight for each part number. So, I need to calculate the median based
on
each unique set. The total table is too big for excel that's why I cannot
do
it in excel.

Example of the data

pcode cost code

01 A
05 B
07 C
10 D

Within each pcode and cost code combination, there are several hundred
part
numbers with varying weights. I'm trying to calculate the median for each
of
these combinations based on the weight.

Does that clarify what I'm trying to do?

Matt

Why would you need "133 separate tables"? We're not there -- we don't
know
what you're storing in your tables or how.

If you need to calculate medians, have you considered exporting data to
Excel and using Excel's Median() function?

Regards

Jeff Boyce
Microsoft Office/Access MVP

Hi all,

I have a group by query with two fields p_code and cost_code that I
need
to
calculate the median on the weight. I'm running into trouble because
the
table is about 100k records with 133 unique p_code and cost_code
combinations.

With my current median function, it only calculates the weight of the
whole
list and not by my group by combination (pcode and cost_code).

Is there a way to calculate the median using my group by set of data?
If
not, I have to create 133 separate tables and calculate the median in
each
table.

Here's the code I was using to calculate the median (taken from
microsoft
knowledge base and only works for one set of data):

Option Compare Database

Option Explicit

Function Median(tName As String, fldName As String) As Single
Dim MedianDB As DAO.Database
Dim ssMedian As DAO.Recordset
Dim RCount As Integer, i As Integer, x As Double, y As Double, _
OffSet As Integer
Set MedianDB = CurrentDb()
Set ssMedian = MedianDB.OpenRecordset("SELECT [" & fldName & _
"] FROM [" & tName & "] WHERE [" & fldName & _
"] IS NOT NULL ORDER BY [" & fldName & "];")
'NOTE: To include nulls when calculating the median value, omit
'WHERE [" & fldName & "] IS NOT NULL from the example.
ssMedian.MoveLast
RCount% = ssMedian.RecordCount
x = RCount Mod 2
If x <> 0 Then
OffSet = ((RCount + 1) / 2) - 2
For i% = 0 To OffSet
ssMedian.MovePrevious
Next i
Median = ssMedian(fldName)
Else
OffSet = (RCount / 2) - 2
For i = 0 To OffSet
ssMedian.MovePrevious
Next i
x = ssMedian(fldName)
ssMedian.MovePrevious
y = ssMedian(fldName)
Median = (x + y) / 2
End If
If Not ssMedian Is Nothing Then
ssMedian.Close
Set ssMedian = Nothing
End If
Set MedianDB = Nothing
End Function

Thanks in advance for your help.

Best Regards,
Matt

 

[raskew via AccessMonster.com]

The following sub calls a copy of Orders (named Orders3) and [Order Details]
from Northwind.
Grouping on CustomerID, it computes the median value of all OrderIDs. It
creates a temporary
table and populates it with the output, one record per CustomerID.

To test, copy to a standard module and call as shown. It obviously would
have to be modified
to fit your needs, but the code to compute the medians should be valid.

The line: db.Execute "DROP TABLE " & tName & ";" should process even if
there is no table
tName, however it produced an error the first time I tested the code--
something that's never
occured before.

Public Sub xMedian(ptable As String)
'*******************************************
'Re:
http://www.accessmonster.com/Uwe/Fo.../47473/Calculating-median-in-a-group-by-query

'Purpose: Return median value and record count of each group in a recordset
'Coded by: raskew
'Inputs: call xMedian("tblTempMedian")<enter>
'Output: WOLZA $216.80--16 (outputs for each group)
'
'*******************************************
Dim db As DAO.Database
Dim rs As DAO.Recordset
Dim rs2 As DAO.Recordset
Dim booEnd As Boolean
Dim curHold As Currency
Dim curKeep As Currency
Dim n As Integer
Dim strSQL As String
Dim strSys As String
Dim tName As String
Dim IDHold As Variant

Set db = CurrentDb

booEnd = False 'rs.EOF flag
strSQL = "SELECT Orders3.CustomerID" _
& ", ([UnitPrice]*[Quantity])+[Freight] AS OrderT " _
& "FROM " _
& "Orders3 " _
& "INNER JOIN " _
& "[Order Details] " _
& "ON " _
& "Orders3.OrderID = [Order Details].OrderID " _
& "Group by " _
& "Orders3.CustomerID " _
& ", ([UnitPrice]*[Quantity])+[Freight] " _
& "ORDER by " _
& "Orders3.CustomerID " _
& ", ([UnitPrice]*[Quantity])+[Freight];"

Set rs = db.OpenRecordset(strSQL)

'******************************************
'Create/recreate holding table
tName = ptable
strSys = SysCmd(acSysCmdSetStatus, "Creating tables...")
'remove table -- if it exists in the database
db.Execute "DROP TABLE " & tName & ";"

'create new table
strSQL = "CREATE TABLE " & tName & " " _
& "( GroupID Text(10)" _
& ", Median Currency" _
& ", RecCount Integer" _
& " )"
db.Execute strSQL
db.TableDefs.Refresh

'******************************************
Set rs2 = db.OpenRecordset(tName)

Do While Not rs.EOF
If rs.EOF = True Then Exit Do
IDHold = rs!CustomerID
n = 0
curHold = 0
Do While rs!CustomerID = IDHold
n = n + 1
curHold = curHold + rs!OrderT
rs.MoveNext
booEnd = rs.EOF
If booEnd = True Then Exit Do
Loop
rs.Move -(Int(n / 2) + 1)
If n Mod 2 = 1 Then 'odd number of elements
Debug.Print IDHold & " " & Format(rs!OrderT, "Currency") & "--" & n; ""
With rs2
.AddNew
!GroupID = IDHold
!Median = curKeep
!recCount = n
.Update
End With
If booEnd = True Then Exit Do
rs.Move (Int(n / 2) + 1)
Else 'even number of elements
curKeep = rs!OrderT
rs.MoveNext
curKeep = (curKeep + rs!OrderT) / 2
Debug.Print IDHold & " " & Format(curKeep, "Currency") & "--" & n
With rs2
.AddNew
!GroupID = IDHold
!Median = curKeep
!recCount = n
.Update
End With

If booEnd = True Then Exit Do
rs.Move Int(n / 2)
End If
Loop
rs.Close
rs2.Close
db.Close

End Sub
'******************************************

HTH - Bob

Matt

Perhaps I'm missing something. I still don't understand why you have "133
tables".

Regards

Jeff Boyce
Microsoft Office/Access MVP

Jeff,

[quoted text clipped - 105 lines]