Page 1 of 3 123 LastLast
Results 1 to 15 of 40

Quartile Function in Access

  1. #1
    tennisbuck is offline 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. #2
    June7's Avatar
    June7 is online now Moderator
    Windows 7 64bit Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    29,467
    "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'.
    To provide db: Make copy, remove confidential data, run compact & repair, zip if large - 2mb allowed, attach to post. Attachment Manager is below the Advanced post editor window.
    If suggestion in this post resolves your issue, please use the Thread Tools and mark the thread as Solved!

    Debug!Debug!Debug! http://www.cpearson.com/excel/debug.htm

  3. #3
    tennisbuck is offline 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. #4
    AlexHedley's Avatar
    AlexHedley is offline Competent Performer
    Windows 7 64bit Access 2010 32bit
    Join Date
    Apr 2013
    Location
    England
    Posts
    179

    Quartile Function in Access

    Alex Hedley

  5. #5
    June7's Avatar
    June7 is online now Moderator
    Windows 7 64bit Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    29,467
    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?
    To provide db: Make copy, remove confidential data, run compact & repair, zip if large - 2mb allowed, attach to post. Attachment Manager is below the Advanced post editor window.
    If suggestion in this post resolves your issue, please use the Thread Tools and mark the thread as Solved!

    Debug!Debug!Debug! http://www.cpearson.com/excel/debug.htm

  6. #6
    tennisbuck is offline 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. #7
    June7's Avatar
    June7 is online now Moderator
    Windows 7 64bit Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    29,467
    Look at the other thread. I posted a possible solution you should be able to adapt.
    To provide db: Make copy, remove confidential data, run compact & repair, zip if large - 2mb allowed, attach to post. Attachment Manager is below the Advanced post editor window.
    If suggestion in this post resolves your issue, please use the Thread Tools and mark the thread as Solved!

    Debug!Debug!Debug! http://www.cpearson.com/excel/debug.htm

  8. #8
    tennisbuck is offline 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. #9
    June7's Avatar
    June7 is online now Moderator
    Windows 7 64bit Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    29,467
    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?
    To provide db: Make copy, remove confidential data, run compact & repair, zip if large - 2mb allowed, attach to post. Attachment Manager is below the Advanced post editor window.
    If suggestion in this post resolves your issue, please use the Thread Tools and mark the thread as Solved!

    Debug!Debug!Debug! http://www.cpearson.com/excel/debug.htm

  10. #10
    tennisbuck is offline 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. #11
    June7's Avatar
    June7 is online now Moderator
    Windows 7 64bit Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    29,467
    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.
    To provide db: Make copy, remove confidential data, run compact & repair, zip if large - 2mb allowed, attach to post. Attachment Manager is below the Advanced post editor window.
    If suggestion in this post resolves your issue, please use the Thread Tools and mark the thread as Solved!

    Debug!Debug!Debug! http://www.cpearson.com/excel/debug.htm

  12. #12
    tennisbuck is offline 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?
    Attached Files Attached Files

  13. #13
    June7's Avatar
    June7 is online now Moderator
    Windows 7 64bit Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    29,467
    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.
    To provide db: Make copy, remove confidential data, run compact & repair, zip if large - 2mb allowed, attach to post. Attachment Manager is below the Advanced post editor window.
    If suggestion in this post resolves your issue, please use the Thread Tools and mark the thread as Solved!

    Debug!Debug!Debug! http://www.cpearson.com/excel/debug.htm

  14. #14
    tennisbuck is offline 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.
    Attached Files Attached Files

  15. #15
    June7's Avatar
    June7 is online now Moderator
    Windows 7 64bit Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    29,467
    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?
    To provide db: Make copy, remove confidential data, run compact & repair, zip if large - 2mb allowed, attach to post. Attachment Manager is below the Advanced post editor window.
    If suggestion in this post resolves your issue, please use the Thread Tools and mark the thread as Solved!

    Debug!Debug!Debug! http://www.cpearson.com/excel/debug.htm

Page 1 of 3 123 LastLast
Please reply to this thread with any new information or opinions.

Similar Threads

  1. Using the Nz function (Access 07)
    By jonny3000 in forum Access
    Replies: 5
    Last Post: 11-15-2012, 10:46 AM
  2. dcount function - new to Access VBA
    By jillp in forum Programming
    Replies: 7
    Last Post: 09-20-2012, 05:35 AM
  3. function in access
    By ali zaib in forum Access
    Replies: 3
    Last Post: 01-14-2012, 11:09 AM
  4. Help with Access - IIf Function
    By cs93 in forum Programming
    Replies: 7
    Last Post: 03-19-2011, 10:52 AM
  5. to_char function in Access?
    By Amber_1977 in forum Queries
    Replies: 0
    Last Post: 01-20-2009, 02:45 PM

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