Calculating median in a group by query

M

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
 
J

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
 
M

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?

Jeff Boyce said:
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 said:
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
Click to expand...
Click to expand...
 
J

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

Matt said:
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?

Jeff Boyce said:
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 said:
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
Click to expand...
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

median 31
Calculation for Median 3
Median in Report 1
Median in Report 1
Calculating the Median for specified groups 1
Please help 6
Statistical Median Code 3
Problem w/ select statement in open recordset 2

Top