# Quartile Function in Access

1. Advanced Beginner
Windows Vista Access 2007
Join Date
Jun 2012
Posts
51

## Quartile Function in Access

Hi Board,

Really stumped and was hoping for some help. Most likely this has to be done in VBA and this is not my strong point in data analysis. I basically have a table in this format:

Sold To Marketing Name Total Sales Dollars
10009 Mufflers 100,000

My question is how would I create quartiles for each marketing name for the sold to's based on the sales dollars? Also are Fish Tail graphs a option in Access?

Thanks

Mike

Also Excel isnt a option since its 1m rows

2. "Fish Tail", i.e. fish curve. Never seen this so went to Wiki. I suppose if you can calculate a dataset of x/y pairs, they could be plotted. Whether or not Access graphing engine could properly draw the line is another matter. I have doubts.

Need more help on what you mean by 'quartiles'.

3. Advanced Beginner
Windows Vista Access 2007
Join Date
Jun 2012
Posts
51
Thanks. I agree with the graph.

Quartile is 25% of items are in a range 50% 75% 100%. Here's what wikipedia says http://en.wikipedia.org/wiki/Quartile

Also heres some code I found, I just don't know how to layer in the market name.

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(Master, Test, _
"DCount(""*"", """ & Master & """, """ & Test & _
"<="" & [" & Test & " ]) >= " & _
X * DCount("*", Test))
End Function
' ***************Code start**************

4. ## Quartile Function in Access

5. What is it with percentiles this week!? The 3rd thread in 2 days referencing the same function. http://www.accessforums.net/queries/...ile-40273.html

What are Master and Test? Why isn't the code using the declared variables?

6. Advanced Beginner
Windows Vista Access 2007
Join Date
Jun 2012
Posts
51
Hi June7,

Master is the table name and test is the field I want for quartiles.

7. Look at the other thread. I posted a possible solution you should be able to adapt.

8. Advanced Beginner
Windows Vista Access 2007
Join Date
Jun 2012
Posts
51
Here's what the table looks like. So for the muffler Marketing name sold 1009 would be the first quartile and 1001 would be the second based on one year total sales.

 Sold To Material Marketing Name Sales November Sales October Sales September Sales August Sales July Sales June Sales May Sales April Sales March Sales Feb Sales Jan Sales Dec One Year Total Quartile Mean Negative 3 Standard Deviation Negative 2 Standard Deviation Standard Deviation 2 Standard Deviation 3Standard Deviation Trend 10009 12 Muffler \$ 100.00 \$ 50.00 \$ 18.00 \$ 100.00 \$ 1,000.00 \$ 50.00 \$ 100.00 \$ 12.00 \$ 500.00 \$ 100.00 \$ 100.00 \$ 10.00 \$ 2,140.00 10001 13 Muffler \$ 25.00 \$ 30.00 \$ 50.00 \$ 230.00 \$ 40.00 \$ 20.00 \$ 40.00 \$ 25.00 \$ 55.00 \$ 100.00 \$ 12.00 \$ 100.00 \$ 727.00 10009 14 Tire \$ 40.00 \$ 43.00 \$ 46.00 \$ 49.00 \$ 52.00 \$ 55.00 \$ 58.00 \$ 61.00 \$ 64.00 \$ 67.00 \$ 70.00 \$ 73.00 \$ 678.00 10001 15 Tire \$ 100.00 \$ 102.00 \$ 104.00 \$ 106.00 \$ 108.00 \$ 110.00 \$ 112.00 \$ 114.00 \$ 116.00 \$ 118.00 \$ 120.00 \$ 122.00 \$ 1,332.00 10009 16 Door \$ 500.00 \$ 498.00 \$ 496.00 \$ 494.00 \$ 492.00 \$ 490.00 \$ 488.00 \$ 486.00 \$ 484.00 \$ 482.00 \$ 480.00 \$ 478.00 \$ 5,868.00 10001 17 Door \$ 10.00 \$ 11.00 \$ 12.00 \$ 13.00 \$ 14.00 \$ 15.00 \$ 16.00 \$ 17.00 \$ 18.00 \$ 19.00 \$ 20.00 \$ 21.00 \$ 186.00

9. What do you mean by 'first' and 'second' - that you want the value calculated for each MarketingName/SoldTo group?

I know very little about statistical analysis. What values should be in the Quartile field? Do you know the formula?

Your data is not a normalized structure. That will probably complicate any solution, as you discovered in other thread http://www.accessforums.net/access/h...row-29040.html. If the data were normalized, VBA would not have been need for that issue. A query could have accomplished.

This data can be rearranged into a normalized structure with a UNION query. There is no wizard or designer for UNION, must type into SQL View of query builder. There is a limit of 50 SELECT lines.

SELECT Material, [Sold To], [Marketing Name], 11 AS SalesMonNum, "November" AS SalesMonName, [Sales November] AS Sales FROM tablename;
UNION SELECT Material, [Sold To], [Marketing Name], 10, "October", [Sales October] FROM tablename
...;

Now use that query as the data source for subsequent aggregate analyses, perhaps even with the function solution from the other thread referenced in post 5.

Otherwise, review http://support.microsoft.com/kb/209839

Why are the month fields arranged in that order? Is this database only one year's worth?

10. Advanced Beginner
Windows Vista Access 2007
Join Date
Jun 2012
Posts
51
Hi June7,

A quartile basically divides a group in this cast (Marketing Name) into 4 equal parts and will show which quartile is in the 1st, 2nd, 3rd, and fourth quartile. So lets say we had 100 records there would be roughly 25 sold to's in each quartile.

The months are arranged like that because thats the way they come out of our data warehouse.

For the Union query do I just copy and paste your code below and paste into the union option?

11. That doesn't seem to be quite what the other poster wanted and sounds harder. They wanted percentile value. You want to assign a quartile group number to each record. What I am not clear about is what should constitute a 'record'. I see only 2 records for each MarketingName value. Would there be more for each in complete dataset? Do you want the quartile grouping to be by MarketingName/SoldTo/Month?

If you want to divide records into groups (either as they currently are or as normalized by UNION), review this info about ranking/numbering records: http://allenbrowne.com/ranking.html

Almost just copy/paste, I don't know your table name. Instead of the ellipsis (...) continue with the other months. Copy/paste and edit as needed.

12. Advanced Beginner
Windows Vista Access 2007
Join Date
Jun 2012
Posts
51
Awesome. I used the Access DB page and now I have the following results in my table.

I read through the rankings page and it was hard for me to follow. Do you have any suggestions?

13. I am still not clear on what you want. You haven't explicitly answered my questions. Provide an example of the output you want to produce.

I don't see more than 3 records for each SoldTo.
Last edited by June7; 12-22-2013 at 04:54 PM.

14. Advanced Beginner
Windows Vista Access 2007
Join Date
Jun 2012
Posts
51
Sorry about not being very clear. Here's an example attached below. Let me know if I need to add anything else.

15. I don't know the algorithm that the Excel Quartile function uses. How does it come up with 3225.24 for the 1st quartile for Doors? None of the data rows have that value and no sum of any combination of rows will return that value.

If you can provide a plain English description of the algorithm, might be able to translate to code. If you calculate the quartiles manually, what would be the exact steps?

Page 1 of 3 123 Last

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•
Tech Forums: Microsoft Office Forums