Semi-Complicated Median Query

[gcgoode]

So I want a query to find the median. I've searched around and found some code but none do exactly what I want, and as a beginner I've had not luck modifying the code so far.



So heres what I have displaying in my datasheet right now: An expression that is the difference between two fields that I call Rate[Premium]-[Fees]), grouped by three other fields; age,sex,marital. I want a query to take the median of the Rate expression, grouped by the other three fields. All the code that I have found doesn't work. It usually states in the VBA that the code must specify the field and table name and that doesn't really work when I'm trying to find the median of another query.

I hope this makes a little bit a sense. Like I said, total beginner here. Any help would be greatly appreciated.

[June7]

Treat a query like a table. Specify the query name.

[gcgoode]

Treat a query like a table. Specify the query name.

Thanks for the repy

Heres what I have right now

Function MedianF(pquery As String, pfield As String) As Single

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

SELECT Driver.Age, Driver.Sex, Driver.Marital, [TotalPremium]-[PolicyFee] AS Rate1, MedianF("Rate Query","Rate1") AS Expr2
FROM (Policy INNER JOIN Car ON Policy.RecordID = Car.PolicyLinkID) INNER JOIN Driver ON Policy.RecordID = Driver.PolicyLinkID;

And when I run it I get a runtime error 3131; Syntax Error in from clause. When I click debug it brings me to my module and highlights "Set rs = CurrentDb.OpenRecordset(strSQL)".

So I think I still have something wrong with how I have formatted the module. I have been playing around with it and no luck so far. Any thoughts?

[orange]

I don't think ptable is dimmed. Try changing to pquery.

[June7]

Make the variable name more generic. Shouldn't matter if the procedure is passed a name of table or query.

Function MedianF(pDataset As String, pField As String) As Single

strSQL = "SELECT " & pField & " FROM " & pDataset & " WHERE " & pField & ">0 ORDER BY " & pField & ";"

[gcgoode]

Make the variable name more generic. Shouldn't matter if the procedure is passed a name of table or query.

Function MedianF(pDataset As String, pField As String) As Single

strSQL = "SELECT " & pField & " FROM " & pDataset & " WHERE " & pField & ">0 ORDER BY " & pField & ";"

Would I still call it by entering in the field "Expr2: MedianF("Rate Query","Expr1")"? Right now I am getting "Too few parameters. Expected 1"

[June7]

The query name has a space. This is causing issue. If names include spaces or special character/punctuation (underscore is exception) must be delimited with brackets [].

[gcgoode]

The query name has a space. This is causing issue. If names include spaces or special character/punctuation (underscore is exception) must be delimited with brackets [].

Thanks. Once again you were right and that fixed the issue.

Now access is getting mad that the Premium and Fees fields from my Expression1(Rate) that I mentioned in my first post aren't in my group by. But if I give them their own group by fields access returns every single rate instead of grouping them by Age,Sex,Marital. And it also seems like my median function is giving me the median of all the data as opposed to just the median of the rate of all 18 Single Males, 19 Single Males etc...

Ahhh I did not think a "simple" median function would take most of my day. You guys have been life savers so far.

[June7]

Where did you get your code - is it all you?

Review https://msdn.microsoft.com/en-us/lib...or=-2147217396

[gcgoode]

Some of it is me, some of it I pieced together from another forum I got by searching my issue. I saw that link when I searched it but I thought it might be showing how to create a form that solves for median. This other way seemed to be better to just use in a query and to solve the median of another expression in that query. But I guess not. I can try to play around with the microsoft links code and see if I can get it to work.

[gcgoode]

So Ive simplified my query to try to get something to work. Heres what it looks like right now

VBA:

Function MedianF(pDataSet As String, pField As String) As Single

Dim rs       As Recordset
Dim strSQL   As String
Dim n        As Long
Dim sglHold  As Single

    strSQL = "SELECT " & pField & " FROM " & pDataSet & " 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

Query:

SELECT MedianF("Rate","TotalPremium") AS Expr2, Driver.Age, Driver.Sex, Driver.Marital
FROM ((Policy INNER JOIN Car ON Policy.RecordID = Car.PolicyLinkID) INNER JOIN Driver ON Policy.RecordID = Driver.PolicyLinkID) INNER JOIN Rate ON Policy.RecordID = Rate.PolicyLinkID
GROUP BY Driver.Age, Driver.Sex, Driver.Marital;

The code runs but it gives me the median of all the data in the TotalPremium field. I want it to give me the median of the totalpremium of all 18 Single Males, 19 single Males, 18 Married Femals etc...
I suspect I need to change something with the where clause in my code but haven't had any luck so far. Can anyone give me pointers? When I run the AVG function it works just fine.

[orange]

I suspect I need to change something with the where clause in my code but haven't had any luck so far.

Without looking at your code in any detail, it seems you are correct.

This snippet(below) (to me) says get the median rate of TotalPremium

MedianF("Rate","TotalPremium") AS Expr2....

The logic you are looking for is something like this:

I have a number of individuals and I want to get the median (xxx) for certain groups.
So you have to identify the group, then do the calculation for individuals in that group.

Get the group of 19 yr old males, then get the median(XXX)

So you need to identify the group when you call your Median function

Good luck.

[gcgoode]

The logic you are looking for is something like this:

I have a number of individuals and I want to get the median (xxx) for certain groups.
So you have to identify the group, then do the calculation for individuals in that group.

Get the group of 19 yr old males, then get the median(XXX)

So you need to identify the group when you call your Median function

Good luck.

Isn't that what am I doing by grouping by the fields age, sex, and marital? I don't really know what Im doing in terms of VBA, but I know that how it works when I use built in functions in Access like AVG.

So are you saying I need to change something in my VBA code?

[orange]

Post a copy of your database, along with a sample of what you want as output.

[gcgoode]

I wish I could post the database I know that would make it 100x easier for someone to help me, but I can't. Its made up of some sensitive information.

I have a Driver table that includes information on driver such as Age, Sex, and Marital Status. I have a rate table that includes information on what they pay like TotalPremium. They are connected by a Primarytable called Policy.


I want the output to look like this

Sex Age Marital MedianofTotal Premium
M 18 S 320
M 19 S 300

Etc.


As there are multiple 18 single Males in my database, I want this query to group them together and find the median. I realize it might be impossible for you to help me without more information.


I've tried using this sample code that June suggested (https://msdn.microsoft.com/en-us/lib...or=-2147217396). But ive had problems with that as well.