Trying to perform calculations on groups of data with code

[funkykizzy]

Hi all!

Is there an easy way to apply a public function to data that has been grouped by 4 parameters?

Basically, I want to group my data by Fields (1) through (4), run a calculation on the data by these groups, and then result in a new table/report with grouped fields and associated values.



Any thoughts?

Thanks!

[Dal Jeanis]

That's exactly how the aggregate functions (Sum, Avg, Min, Max, First, Last) are used. http://www.techonthenet.com/access/functions/

You can always make a table of the results of a query, so that's not a problem.

Beyond that, you'll have to be much more specific about your desired "public function". I have no idea what you are thinking when you type that term.

[June7]

Is this question related to https://www.accessforums.net/queries...ile-40273.html

[funkykizzy]

Hi June7 -- Yes, this is the same problem, but I was trying to see if I couldn't write my own percentile code using the Excel algorithm mentioned in that other post.

Excel Percentiles Algorithm
(N-1)*P = k + d, where N = record count, P = percentile with 0<P<1, and k = integer part and d = decimal part
v_(k+1) + d*(v_k+2 - v_k+1) = our desired percentile. Where v_k is the value at the k position/rank

Using the data in my example database, I am trying to perform this calculation by the groupings. So, for the group of State, Method, Parameter, Year being a, a, a, 2001, the ranked values are 4, 34, and 56.
Applying the Excel equations to get the 90th percentile we have (N-1)*P = (3-2)*0.90 = 1.8. So k = 1 and d = 0.8.
Looking at our ranked values, v_(k+1) = v_2 = 34 and v_(k+2) = v_3 = 56.
Finishing the calculations we have 34 + 0.8(56 - 34) = 51.6.
The value in the "value" field of either a Make Table query or report is returning as 93 for all the groups, which is the 90th percentile of the ENTIRE database.

I just saw that you replied to that other thread and I will go check that out.

Hi Dal -- As you can probably tell by now, I am trying to calculate percentiles by writing my own code in Access (since there is no easy way to do this in Access already). See the link June7 provided above to see more information about this issue.

Thanks!!

[funkykizzy]

Hi all!!

The solution that June7 provided worked like a dream! With my dummy data AND with my actual/working database. My working database is large so the query took a while to run (about 20 - 30 minutes), but it worked, which is what I needed for now. To see the solution from June7, go to this thread:https://www.accessforums.net/queries...tml#post204233

Thanks!

[Dal Jeanis]

First, I renamed the fields because using reserved words like Year and Value makes me very nervous.

Table1
   MyID
   MyState
   MyMethod
   MyParameter
   MyYear
   Samples
   MyValue

Second, I created a count query to get the count of records in each group, and a ranking query (qryGroupRank) to give you each record's ordinal rank within its group. Note that in case of tie Values, MyRank has an arbitrary tiebreaker to be used for calculating percentile, and MyTrueRank gives both tied records the same rank.

qryGroupCount:
SELECT 
   MyState,
   MyMethod,
   MyParameter, 
   MyYear,
   Count(MyID) AS MyNVal
FROM 
   Table1
GROUP BY    
   MyState,
   MyMethod,
   MyParameter, 
   MyYear;

qryGroupRank:
SELECT 
   T1.MyID, 
   First(T1.MyState) AS MyState, 
   First(T1.MyMethod) AS MyMethod, 
   First(T1.MyParameter) AS MyParameter, 
   First(T1.MyYear) AS MyYear, 
   First(T1.MyValue) AS MyValue,    
   SUM( IIF(   (T1.MyValue>T2.MyValue OR (T1.MyValue = T2.MyValue AND T1.MyID>=T2.MyID)),1,0)) AS MyRank,
   SUM( IIF( (T1.MyValue>T2.MyValue),1,0)) + 1 AS MyTrueRank
FROM 
   Table1 AS T1 
   INNER JOIN 
   Table1 AS T2 
   ON ((T1.MyYear = T2.MyYear) 
   AND (T1.MyParameter = T2.MyParameter) 
   AND (T1.MyMethod = T2.MyMethod) 
   AND (T1.MyState = T2.MyState))
GROUP BY T1.MyID;

Third, I created a static table of the constants used to calculate percentiles given any particular group size. Here, I'm giving you the calc rather than the values, obviously. Just load enough for the largest group you anticipate.

tblNVals
   NVal    Number    3  thru 10  (as many records as you want)
   PNum    Number    90          (and any other percentiles you want)
   PVal    Currency  (NVal-1)*PNum/100      
   KVal    Number    Int(PVal)
   K1Val   Number    KVal+1
   DVal    Currency  PVal-KVal

Next, for each group, we join qryGroupCount to tblNVals to get the k-numbers of the two values we need to pull, then join to qryGroupRank for the K and K+1 record, to calculate the RankValue for 90th percentile.

qryRankValsByGroup;
SELECT
   QG.MyState,
   QG.MyMethod,
   QG.MyParameter, 
   QG.MyYear,
   QG.MyNVal,
   TN.KVal,
   TN.K1Val,
   TN.DVal,
   Q1.MyValue,
   Q2.MyValue,
   (Q1.MyValue + TN.DVal*(Q2.MyValue - Q1.MyValue)) As RankValue
FROM
  ((qryGroupCount AS QG
   INNER JOIN
   tblNVals AS TN
   ON TN.NVal = QG.MyNVal)
   INNER JOIN 
   qryGroupRank AS Q1 
   ON (Q1.MyYear = QG.MyYear) 
      AND (Q1.MyParameter = QG.MyParameter) 
      AND (Q1.MyMethod = QG.MyMethod) 
      AND (Q1.MyState = QG.MyState)
      AND (Q1.MyRank = TN.KVal))
   INNER JOIN 
   qryGroupRank AS Q2 
   ON (Q2.MyYear = QG.MyYear) 
      AND (Q2.MyParameter = QG.MyParameter) 
      AND (Q2.MyMethod = QG.MyMethod) 
      AND (Q2.MyState = QG.MyState)
      AND (Q2.MyRank = TN.K1Val)
WHERE PNum = 90;

These calculations run in a flash on your test database.

[justinhampton81]

Hi Dal,

I tried to implement the code and queries that you provided above, but I must have misunderstood something because the queries are coming back all null. Would you be so kind to glance at my implementation and critique? Thanks in advance.
Attachment 18815