Quartiles

[stephan1988]

Hello Guys,

Reference https://www.accessforums.net/search....e=vBForum_Post

I have exactly the same problem as gtg430i. I've already tried everything what is proposed in this thread, unfortunately though, I keep getting error messages.. Hopefully one of you guys can help me..

I have around 200.000 records that contain information on Tenders. Each tender is identified with a SourcingPartFactory number and in an additional colum I have a price that was offered by a certain supplier. To see how my data looks like, see the screenshot I've made.



Same as gtg430i, I would like to calculate the 25th and 75th quartile by means of the code as provided below:


' ***************Code start**************
' This code was originally written by Michel Walsh.


' It is not to be altered or distributed,
' except as part of an application.
' You are free to use it in any application,
' provided the copyright notice is left unchanged.
'
' Code courtesy of
' Michel Walsh
'
Public Function XPercentile(FName As String, _
TName As String, _
X As Double) _
As Double
' FName = Field name
' TName = Table name
' x = decimal percentile (0.68 for 68%)

' Return the minimum value for which x% of
' the values are lower or equal to it
XPercentile = DMin(FName, TName, _
"DCount(""*"", """ & TName & """, """ & FName & _
"<="" & [" & FName & " ]) >= " & _
X * DCount("*", TName))
End Function
' ***************Code start**************

Since I'm an absolute beginner in Access Programming I've just literally copy pasted this into an Access Module, whithout doing anything to it.

Now I would like to use a query to determine (first) the 25th and (in a second query) the 75th quartile. I've set up my query in the following way:



Though, as you can see, Access comes up with an error. First I thought it would be my database that is too complex/messy that caused the problem. So I made a new database with just 10 rows (five times group A, five times group B, with different prices for each record), however, Access came up with the same error.

When pushing "ok" the following screen comes up:



Ive tried to Google what it means, but unsuccesfull.. Pushing the "debug" results in the following:



The fact that it happens with my "real" database and the small one I made up just to check whether its my database that is causing the problem still gives me hope that one day I will tackle this problem. But therefore I do need your help!!

Could someone please help me on how to calculate the 25th and 75th quartile in my database (see image for the structure).! I am really desperate.. I've been searching all day to find a solution, but the code that is written above seems to be the only solution..

Many thanks in advance!

Stephan

[ranman256]

you dont need all those double quotes....Dcount("*","tbl",where)
and you dont put the formula in "Dcount" quotes either. (it becomes a string).

[ItsMe]

you dont need all those double quotes....Dcount("*","tbl",where)
and you dont put the formula in "Dcount" quotes either. (it becomes a string).

I believe that is VBA that is an UDF called from a query object.

[stephan1988]

Hello ranman256,

what do you mean by "you dont need all those double quotes".. In the code that is provided by michel walsh? Do you mean this:

Percentile = DMin(FName, TName, _"DCount(""*"", """ & TName & """, """ & FName & _"<="" & [" & FName & " ]) >= " & _X * DCount("*", TName))

As said, I am a real beginner in this so hopefully you can help me out with a suggestion how how to set up the code in the module and how to set up my expression in the query.

Many thanks in advance!

Stephan

[ItsMe]

Stephan,

It seems you are on the right track by creating a separate database to create a working model. You are also doing the correct thing by clicking the Debug button and observing where the exception is being thrown.

If you look at the highlighted code, you will see that the code is looking for values of variables. In debug mode , you should be able to hover the variables to se what the values of variables are.

Try hovering over Tname and Fname and make sure there are values. Make sure that the String variables Tname and Fname have been initialized.

If you look a few lines above the highlighted code, you will see the opening line for the function. It is asking for parameters. The function needs parameters passed to it in order to run/function. It is looking for three arguments as parameters, a name of a table "Tname", name of a Field "Fname", and value of a field X.

Hope this info helps to point you in the correct direction.

[stephan1988]

Hello ItsMe,

that is correct. It is a UDF (saved in a Module), which is then called from within a query..

Stephan