Quartile Function in Access

[tennisbuck]

Here you go:

http://www.mathsteacher.com.au/year9.../quartiles.htm

Once I got the quartile I just filled in the ranges. So for Min I just labeled everything with Min value, 1st quartile everything up the 2nd quartile, 2nd quartile I labeled values 2 that went up to the 3rd quartile, and so forth.

[tennisbuck]

This is the simplest quartile explanation. The question is can this be translated to VBA? If not I can look for another calculation. From looking at it the difficult part might be finding N (maybe a count by Marketing Name) then ordering the values from lowest to highest?

Will this code help in the sorting:

xpression .SetOrderBy(OrderBy, ControlName)
expression A variable that represents a DoCmd object.
Parameters

Name	Required/Optional	Data Type	Description
OrderBy	Required	Variant	A string expression that includes the name of the field or fields on which to sort records and the optional ASC or DESC keywords.
ControlName	Optional	Variant	If provided and the active object is a form or report, the name of the control that corresponds to the subform or subreport that will be sorted. If empty and the active object is a form or report, the parent form or report is sorted.

[June7]

This as been quite a learning experience. I am not finding much that pertains to quartile calculation in Access VBA.http://www.ehow.com/info_12164910_qu...ns-access.html
This thread shows how to determine the quartiles of an array using Excel function called in VBA. http://www.ozgrid.com/forum/showthread.php?t=98152

So if what you want is the quartiles of the months for each record of your non-normalized table, function can be written for that then call the function from query.

SELECT *, Quartile(1, Array([Sales January], [Sales February], ...)) As FirstQuartile FROM tablename;

Function Quartile(intQ As Integer, ParamArray X() As Variant)
Dim ary As Variant
ary = X()
Set wf = Excel.Application.WorksheetFunction
Quartile = wf.Quartile(ary, intQ)
End Function

I tested the quartile function in the VBA Immediate window with:
Debug.Print Quartile(1, Array(18, 20, 23, 19, 27, 24, 28))
which returns a value of 19.5

Calculating the quartiles on a set of records will be more complicated, involving looping through a recordset which is what is actually done in the other thread I referenced earlier where they are calculating percentile. Do you want to sum the months and use that value for the quartile calculation? Using the function from that thread, with modification for your table, can calculate the 25%, 50%, 75% quartiles for each MarketingDescription group annual sales. Could also calc the minimum and maximum with DMin and DMax functions. Then maybe use that query in another query to calculate which quartile each record belongs in. Maybe a nested IIf expression or if too complicated, another function.

Query1
I think you already have this one which is summing all the months to get a total annual sales. Create new fields with expressions for the quartiles (i.e. percentiles) using the function from other thread for each quartile.
25th: PercentileRst("tablename", "Total", .25, [Marketing])
Modify that function as follows:
Public Function PercentileRst(RstName As String, fldName As String, PercentileValue As Double, strCriteria As String) As Double
...
Set RstOrig = CurrentDb.OpenRecordset("SELECT * FROM " & RstName & " WHERE Marketing='" & strCriteria & "'", dbOpenDynaset)
...
End Function

Query2
Use query1 as data source to calculate which quartile group each record belongs with. Maybe like:
Quartile: IIf([MaxTot]<=[Total],4,IIf([75th]<=[Total],3,IIf([50th]<=[Total],2,IIf([25th]<=[Total],1,0))))
The results are almost the same as the example in the worksheet.

[tennisbuck]

Thanks a ton! I get this debug a error and I've tried several different ways to modify it: This way I just put the sum of all months sales (FldSales, from query test 2.

Set RstOrig = CurrentDb.OpenRecordset("SELECT FldSales FROM Test2" & RstName & " WHERE Marketing View Description='" & strCriteria & "'", dbOpenDynaset)

[funkykizzy]

Hey Tennisbuck!

I am having a look at this (not that I can help more than June7, but I'll try).

So you are trying to predict the first quartile sales (10009) and second quartile sales (10001) based on the previous year's sales numbers by month? Is that correct?

[tennisbuck]

Hi Funkykizzy,

I'm trying to show quartile by Marketing Name for sold do and use sales dollars as the ranking. I posted an example below. The code that June 7 used above should work I just need to get this to work:


Set RstOrig = CurrentDb.OpenRecordset("SELECT FldSales FROM Test2" & RstName & " WHERE Marketing View Description='" & strCriteria & "'", dbOpenDynaset)

[tennisbuck]

I'm uploading a DB with the error right now.

[tennisbuck]

For some reason I can't upload the DB after I compacted and repaired+zipped. Here's a table and the error I get when I run the query with this code is:

Run Time Error 3075
Syntax error (missing operator) in query expression . Marketing View Description=Door

Here's the code I used to call the function:

25th: PercentileRst("Test 2","FldSum",0.25,[Marketing View Description])

Here's the function:
Public Function PercentileRst(RstName As String, fldName As String, PercentileValue As Double, strCriteria As String) As Double
'This function will calculate the percentile of a recordset.
'The field must be a number value and the percentile has to
'be between 0 and 1.
If PercentileValue < 0 Or PercentileValue > 1 Then
MsgBox "Percentile must be between 0 and 1", vbOKOnly
End If
Dim PercentileTemp As Double
Dim dbs As Database
Set dbs = CurrentDb
Dim xVal As Double
Dim iRec As Long
Dim i As Long
Dim RstOrig As Recordset
Set RstOrig = CurrentDb.OpenRecordset("SELECT * FROM " & RstName & " WHERE Marketing View Description ='" & strCriteria & "'", dbOpenDynaset)
RstOrig.Sort = fldName
Dim RstSorted As Recordset
Set RstSorted = RstOrig.OpenRecordset()
RstSorted.MoveLast
RstSorted.MoveFirst
xVal = ((RstSorted.RecordCount - 1) * PercentileValue) + 1
'x now contains the record number we are looking for.
'Note x may not be whole number
iRec = Int(xVal)
xVal = xVal - iRec
'i now contains first record to look at and
'x contains diff to next record
RstSorted.Move iRec - 1
PercentileTemp = RstSorted(fldName)
If xVal > 0 Then
RstSorted.MoveNext
PercentileTemp = ((RstSorted(fldName) - PercentileTemp) * xVal) + PercentileTemp
End If
RstSorted.Close
RstOrig.Close
Set RstSorted = Nothing
Set RstOrig = CurrentDb.OpenRecordset("SELECT * FROM " & RstName & " WHERE Marketing View Description ='" & strCriteria & "'", dbOpenDynaset)
Set dbs = Nothing
PercentileRst = PercentileTemp
End Function


Private Sub test()
MsgBox PercentileRst("tbl_Main", "fld_Score", 0.95)
End Sub

[June7]

Take a good look at funkykizzy thread. Another solution was provided which might work better. https://www.accessforums.net/program...ode-40269.html

[funkykizzy]

Hi again,

I found that having spaces in your field names can cause a sticky situation. For example, the line that you are having trouble with:
Set RstOrig = CurrentDb.OpenRecordset("SELECT FldSales FROM Test2" & RstName & " WHERE Marketing View Description='" & strCriteria & "'", dbOpenDynaset)
make sure that you put your Market View Description in brackets so that it looks like this:
Set RstOrig = CurrentDb.OpenRecordset("SELECT FldSales FROM Test2" & RstName & " WHERE [Marketing View Description]='" & strCriteria & "'", dbOpenDynaset)

Give that a try and see if it works.

[June7]

Where did Test2 come from?

If table name has spaces, will need brackets also. Use brackets regardless just to be safe.

Set RstOrig = CurrentDb.OpenRecordset("SELECT FldSales FROM [" & RstName & "] WHERE [Marketing View Description]='" & strCriteria & "'", dbOpenDynaset)

It is advisable to avoid spaces and special characters/punctuation (underscore is exception) in naming. Better would be Marketing_View_Description or MarketViewDescription.

[tennisbuck]

Thanks to both of you: Does this look right because its still bombing out in the module.

Set RstOrig = CurrentDb.OpenRecordset("SELECT [FldSales] FROM [" & [Test 2] & "] WHERE [Marketing View Description]='" & strCriteria & "'", dbOpenDynaset)

[tennisbuck]

Here's the sample DB.

[June7]

No.

This is hard-coding the table name and not concatenating variable.

Set RstOrig = CurrentDb.OpenRecordset("SELECT [FldSales] FROM [Test 2] WHERE [Marketing View Description]='" & strCriteria & "'", dbOpenDynaset)

[tennisbuck]

How about this:

Set RstOrig = CurrentDb.OpenRecordset("SELECT FldSales FROM [Test 2] [" & RstName & "] WHERE [Marketing View Description]='" & strCriteria & "'", dbOpenDynaset)

It seems now the error out here and it says COMPILE ERROR Method or Data member not found.

Public Function PercentileRst(RstName As String, fldName As String, PercentileValue As Double, strCriteria As String) As Double