I am using the following vba code to calculate the median of a table
in MS Access:
Public Function MedianOfRst(RstName As String, fldName As String) As
Double
'This function will calculate the median of a recordset. The
field must be a number value.
Dim MedianTemp As Double
Dim RstOrig As Recordset
Set RstOrig = CurrentDb.OpenRecordset(RstName, dbOpenDynaset)
RstOrig.Sort = fldName
Dim RstSorted As Recordset
Set RstSorted = RstOrig.OpenRecordset()
If RstSorted.RecordCount Mod 2 = 0 Then
RstSorted.AbsolutePosition = (RstSorted.RecordCount / 2) - 1
MedianTemp = RstSorted.Fields(fldName).Value
RstSorted.MoveNext
MedianTemp = MedianTemp + RstSorted.Fields(fldName).Value
MedianTemp = MedianTemp / 2
Else
RstSorted.AbsolutePosition = (RstSorted.RecordCount - 1) / 2
MedianTemp = RstSorted.Fields(fldName).Value
End If
MedianOfRst = MedianTemp
End Function
While it works great for calculating the median of an entire row in a
table, I now have the need to calculate the median of several subsets
of data in a table. Say we have a table:
Shift ProcessingTime
Day 2
Night 1
Day 3
Day 5
Night 6
Night 9
I need to be able to return the median processing time of orders
processed during day and during the night. I know I could run 2
queries, one to split the table into day and night orders, then use
the median function, but my real project requires the data to be split
up into 8 categories so this would be rather time consuming and
involve a lot of sub-queries. Any ideas on how to modify the above
query to only calculate the median if another cell in the row meets
certain criteria?
Thanks,
Andrew V. Romero 6 4037 rr*******@icqmail.com wrote:
>I am using the following vba code to calculate the median of a table in MS Access:
Public Function MedianOfRst(RstName As String, fldName As String) As Double
'This function will calculate the median of a recordset. The field must be a number value.
Dim MedianTemp As Double
Dim RstOrig As Recordset
Set RstOrig = CurrentDb.OpenRecordset(RstName, dbOpenDynaset)
RstOrig.Sort = fldName
Dim RstSorted As Recordset
Set RstSorted = RstOrig.OpenRecordset()
If RstSorted.RecordCount Mod 2 = 0 Then
RstSorted.AbsolutePosition = (RstSorted.RecordCount / 2) - 1
MedianTemp = RstSorted.Fields(fldName).Value
RstSorted.MoveNext
MedianTemp = MedianTemp + RstSorted.Fields(fldName).Value
MedianTemp = MedianTemp / 2
Else
RstSorted.AbsolutePosition = (RstSorted.RecordCount - 1) / 2
MedianTemp = RstSorted.Fields(fldName).Value
End If
MedianOfRst = MedianTemp End Function
While it works great for calculating the median of an entire row in a table, I now have the need to calculate the median of several subsets of data in a table. Say we have a table:
Shift ProcessingTime Day 2 Night 1 Day 3 Day 5 Night 6 Night 9
I need to be able to return the median processing time of orders processed during day and during the night. I know I could run 2 queries, one to split the table into day and night orders, then use the median function, but my real project requires the data to be split up into 8 categories so this would be rather time consuming and involve a lot of sub-queries. Any ideas on how to modify the above query to only calculate the median if another cell in the row meets certain criteria?
You can use another argument to specify the grouping field:
Public Function MedianOfRst(RstName As String, _
fldName As String, _
grpName As String) ...
Then you can use the recordset's Filter property to restrict
the function to that one field:
. . .
RstOrig.Sort = fldName
RstOrig.Filter = grpName
. . .
The query would look like:
SELECT Shift,MedianOfRst("table","ProcessingTime","Shift" )
FROM table
GROUP BY Shift
--
Marsh
I gave this a try (with the table name of ShiftTable), but it doesn't
appear to be working. I changed the function code to:
Public Function CondMedian(RstName As String, fldName As String,
grpName As String)
'This function will calculate the median of a recordset. The
field must be a number value.
Dim MedianTemp As Double
Dim RstOrig As Recordset
Set RstOrig = CurrentDb.OpenRecordset(RstName, dbOpenDynaset)
RstOrig.Sort = fldName
RstOrig.Filter = grpName
Dim RstSorted As Recordset
Set RstSorted = RstOrig.OpenRecordset()
If RstSorted.RecordCount Mod 2 = 0 Then
RstSorted.AbsolutePosition = (RstSorted.RecordCount / 2) - 1
MedianTemp = RstSorted.Fields(fldName).Value
RstSorted.MoveNext
MedianTemp = MedianTemp + RstSorted.Fields(fldName).Value
MedianTemp = MedianTemp / 2
Else
RstSorted.AbsolutePosition = (RstSorted.RecordCount - 1) / 2
MedianTemp = RstSorted.Fields(fldName).Value
End If
CondMedian = MedianTemp
End Function
and created a query with the following sql:
SELECT Shift, CondMedian("ShiftTable","ProcessTime","Shift")
FROM ShiftTable
GROUP BY Shift;
when I run this query, it returns:
Day 4
Night 4
so it appears to be taking the median of the whole recordset and not
grouping by Day and Night Shift. I want the unique median for the day
and the unique median for the night shift. Any other ideas?
Thanks,
Andrew V. Romero rr*******@icqmail.com wrote:
>I gave this a try (with the table name of ShiftTable), but it doesn't appear to be working. I changed the function code to:
Public Function CondMedian(RstName As String, fldName As String, grpName As String)
'This function will calculate the median of a recordset. The field must be a number value.
Dim MedianTemp As Double
Dim RstOrig As Recordset
Set RstOrig = CurrentDb.OpenRecordset(RstName, dbOpenDynaset)
RstOrig.Sort = fldName
RstOrig.Filter = grpName
Dim RstSorted As Recordset
Set RstSorted = RstOrig.OpenRecordset()
If RstSorted.RecordCount Mod 2 = 0 Then
RstSorted.AbsolutePosition = (RstSorted.RecordCount / 2) - 1
MedianTemp = RstSorted.Fields(fldName).Value
RstSorted.MoveNext
MedianTemp = MedianTemp + RstSorted.Fields(fldName).Value
MedianTemp = MedianTemp / 2
Else
RstSorted.AbsolutePosition = (RstSorted.RecordCount - 1) / 2
MedianTemp = RstSorted.Fields(fldName).Value
End If
CondMedian = MedianTemp End Function
and created a query with the following sql:
SELECT Shift, CondMedian("ShiftTable","ProcessTime","Shift") FROM ShiftTable GROUP BY Shift;
when I run this query, it returns: Day 4 Night 4
so it appears to be taking the median of the whole recordset and not grouping by Day and Night Shift. I want the unique median for the day and the unique median for the night shift. Any other ideas?
Boy, did I muck that up. That should have been more like:
SELECT Shift, CondMedian("ShiftTable","ProcessTime","Shift="
& Shift)
FROM ShiftTable
GROUP BY Shift
--
Marsh
I gave that a try and it comes back with a runtime error saying Too
few parameters. Expected 2. If I debug, it brings me to the line:
Set RstSorted = RstOrig.OpenRecordset()
Any other things I should try?
Thanks,
Andrew V. Romero
Ah ha, changing the query to:
SELECT Shift, CondMedian("ShiftTable","ProcessTime","Shift='" & Shift
& "'")
FROM ShiftTable
GROUP BY Shift;
worked. Notice the ' that I added before and after the Shift variable
argument. I think this may save me a lot of time. Thanks for your
help. I wish I know some of these tricks better.
-Andrew V. Romero rr*******@icqmail.com wrote:
>Ah ha, changing the query to:
SELECT Shift, CondMedian("ShiftTable","ProcessTime","Shift='" & Shift & "'") FROM ShiftTable GROUP BY Shift;
worked. Notice the ' that I added before and after the Shift variable argument.
The quotes are needed because the Shift field is apparently
a Text field. A number type field would not accept the
quotes.
--
Marsh This thread has been closed and replies have been disabled. Please start a new discussion. Similar topics
by: Hugo L. |
last post by:
I really don't know how to calculate the median. Can anybody help me?
|
by: uspensky |
last post by:
I have a table (cars) with 3 fields:
VIN, Class, sell_price
101, sports, 10000
102, sports, 11000
103, luxury, 9000
104, sports, 11000
105, sports, 11000
106, luxury, 5000
107, sports, 11000
|
by: Sebastien.LICHTHERTE |
last post by:
Hello,
I need to calculate the median and percentile of values in a group by
query satisfying several criteria the user is asked to fill in when
opening the query.
A have a table called RX with...
|
by: Scott |
last post by:
I need to take the median from a field of records in a report. Can someone
shed the light how to do it.
Thanks,
Scott
|
by: Bhadan |
last post by:
Hello,
I have several jagged arrays which have been sorted.
I'm trying to find the median of each array. Any tips appreciated.
TIA.
Bhads.
|
by: brady |
last post by:
I have a function called int calcMedian(int ar, int numElements) How do i code the function to calculate the median in the array? Please help
|
by: CrostonScottish |
last post by:
Has anybody got any ideas or nifty code for calculating the median value in a form.
I currently have a database which we use for post-course evaluations. Part of the evaluation asks the attendees...
|
by: mehwishobaid |
last post by:
i dont know wat is wrong with my code. when i compile. i get the error
saying line 29: error: expression must have pointer-to-object type
#include <iostream>
using namespace std;
#include...
|
by: dmorand |
last post by:
Does anyone have any experience with retrieving a median value using SQL server? I'm trying to retrieve a median value from a list of various numerical values.
Any help would be appreciated.
|
by: lllomh |
last post by:
Define the method first
this.state = {
buttonBackgroundColor: 'green',
isBlinking: false, // A new status is added to identify whether the button is blinking or not
}
autoStart=()=>{
|
by: DJRhino |
last post by:
Was curious if anyone else was having this same issue or not....
I was just Up/Down graded to windows 11 and now my access combo boxes are not acting right. With win 10 I could start typing...
|
by: isladogs |
last post by:
The next Access Europe meeting will be on Wednesday 4 Oct 2023 starting at 18:00 UK time (6PM UTC+1) and finishing at about 19:15 (7.15PM)
The start time is equivalent to 19:00 (7PM) in Central...
|
by: giovanniandrean |
last post by:
The energy model is structured as follows and uses excel sheets to give input data:
1-Utility.py contains all the functions needed to calculate the variables and other minor things (mentions...
|
by: NeoPa |
last post by:
Hello everyone.
I find myself stuck trying to find the VBA way to get Access to create a PDF of the currently-selected (and open) object (Form or Report).
I know it can be done by selecting :...
|
by: NeoPa |
last post by:
Introduction
For this article I'll be using a very simple database which has Form (clsForm) & Report (clsReport) classes that simply handle making the calling Form invisible until the Form, or all...
|
by: Teri B |
last post by:
Hi, I have created a sub-form Roles. In my course form the user selects the roles assigned to the course.
0ne-to-many. One course many roles.
Then I created a report based on the Course form and...
|
by: isladogs |
last post by:
The next Access Europe meeting will be on Wednesday 1 Nov 2023 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM)
Please note that the UK and Europe revert to winter time on...
|
by: isladogs |
last post by:
The next online meeting of the Access Europe User Group will be on Wednesday 6 Dec 2023 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM).
In this month's session, Mike...
| |