Search results

  1. F

    Using a custom aggregate function with the GROUP BY clause

    You are a genius! That works great! ...for the dummy data... Unfortunately, my actual data has varying N-values for each grouping (about 50 to 60) and my client wants the 5th, 50th, and 95th percentiles. So I will take what I have learned from your comments/suggestions and see if I can't...
  2. F

    Using a custom aggregate function with the GROUP BY clause

    Sorry to be such a dunce, plog, but I am assuming that the first code is a subquery called sub_PercentileRank for the second code. Is that correct?
  3. F

    Using a custom aggregate function with the GROUP BY clause

    Thank you thank you thank you!! Wow, that was a lot simpler than what I was trying to do! I'm going to give it a try to see if it works. Thanks again!
  4. F

    Using a custom aggregate function with the GROUP BY clause

    That, my friend, is a VERY good question. Unfortunately, calculating a percentile is a little more complicated than just multiplying by the percent. First, we rank our data: 1 4 2 34 3 56 And the way we are calculating the percentile with Excel is: Let N be the number of values and P be...
  5. F

    Using a custom aggregate function with the GROUP BY clause

    Programmatically - it should be done with my code in Module1 (I can't help myself) Theoretically (to a 6th grader) - if the data was sorted with the largest numbers on the top and the smallest on the bottom, the 90th percentile is the point in the data at which 10% of the bigger numbers are...
  6. F

    Using a custom aggregate function with the GROUP BY clause

    Sorry. OriginalData State, Method, Parameter, Year, Samples, Value a, a, a, 2001, 2, 34 a, a, a, 2001, 3, 56 a, a, a, 2001, 1, 4 a, a, a, 2002, 1, 27 a, a, a, 2002, 4, 38 a, a, a, 2002, 1, 13 a, a, b, 2001, 2, 43 a, a, b, 2001, 1, 25 a, a, b, 2001, 3, 64 etc... Group by: state, method...
  7. F

    Using a custom aggregate function with the GROUP BY clause

    No, unfortunately all the percentiles are not 93. I want it to be calculated by year (in other words, each row should have a different value in the Percentile field). I think it has to do with the "*" in my DCount method in the Criteria parameter of my DMin(,,DCount) method. But when I try to...
  8. F

    Using a custom aggregate function with the GROUP BY clause

    I'm starting to think that it has something to do with the "*" in my Dcount arguments (in Module1). But I'm having trouble solving the issue. Thanks!
  9. F

    Using a custom aggregate function with the GROUP BY clause

    It was in my example database, but I don't think I posted it right. I'll try again. Original data = Table1 Query for calculations = Query1 Results wanted = Percentiles Is that better? Thanks!
  10. F

    Using a custom aggregate function with the GROUP BY clause

    Sure! I am looking to get the Percentiles table populated. Here is what it should look like: Percentiles State, Method, Parameter, Year, Average, Sum, 90thPercentile a, a, a, 2001, ##, ##, ## a, a, a, 2002, ##, ##, ## a, a, b, 2001, ##, ##, ## a, a, b, 2002, ##, ##, ## a, a, c, 2001, ##, ##...
  11. F

    Using a custom aggregate function with the GROUP BY clause

    Hi all! I am trying to calculate annual percentiles of a large set of data and I have only been successful at retrieving the percentile of the entire data set (and not by the grouping). See provided example database for code/query. Query1 is what I want to happen to make the Percentiles...
Back
Top Bottom