473,327 Members | 1,952 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,327 software developers and data experts.

Taking one additional record in percentile query

Access 2003/XP

Hello, I have a query that shows a running total and percentile of the number of claims our company gets for each type of claim. The field expressions in the design of the query look like this:

Type: [Type]
Claims: [Claims]
RunTotal: DSum("Claims","ClaimsType","[Claims] >= " & [Claims])
Percentile: [RunTotal]/80952144
Included: IIf([Percentile]<0.75,"Yes","No")

"RunTotal" is sorted in descending order, "ClaimsType" is the table the query is based off of, 80952144 is the total sum of all claims (entering Sum(Claims) only gives the sum for each "Type").

My question here is with the "Included" field, which takes the cutoff point at everything within the top 75th percentile. What I would like it to do, is if it could somehow take one additional "Type" over .75 to ensure that at least 75% of "claims" is covered. For example, what it shows now is:

Type Claims RunTotal Percentile Included
A 10 10 50% Yes
B 4 14 70% Yes
C 3 17 85% No
D 2 19 95% No
E 1 20 100% No

But I would need it to show "Yes" for Type C, since A and B alone would not cover 75% of the total claims. Also, sometimes there is a "tie" at the cutoff point, which might complicate things further. Then it would look like:

Type Claims RunTotal Percentile Included
A 10 10 50% Yes
B 4 14 70% Yes
C 2 18 90% No
D 2 18 90% No
E 1 20 100% No

In this case I would need both C and D to have "Included" as "Yes". Hopefully this doesn't complicate things too much, and any help would be greatly appreciated.
Aug 4 '10 #1

✓ answered by ADezii

I do believe that I have a solution for at least your first dilemma, namely have the Included Field show 'Yes' for the 1st additional 'Type' over .75 Percentile.
  1. Declare a Public Variable as follows in a Standard Code Module:
    Expand|Select|Wrap|Line Numbers
    1. Public intNumOfTypeOvers As Integer
  2. Copy-N-Paste the following Public Function into a Standard Code Module:
    Expand|Select|Wrap|Line Numbers
    1. Public Function fCalcIncluded(sngPercentile As Single) As String
    2. 'intNumOfTypeOvers must retain Value between successive Calls
    3. If sngPercentile < 0.75 Then
    4.   fCalcIncluded = "Yes"
    5. Else
    6.   intNumOfTypeOvers = intNumOfTypeOvers + 1
    7.     If intNumOfTypeOvers = 1 Then   'and only 1
    8.       fCalcIncluded = "Yes"
    9.     Else
    10.       fCalcIncluded = "No"
    11.     End If
    12. End If
    13. End Function
  3. Change the manner in which the Calculated Field [Included] obtains its Value, namely:
    Expand|Select|Wrap|Line Numbers
    1. 'Outta here! ==> Included: IIf([Percentile]<0.75,"Yes","No")
    Expand|Select|Wrap|Line Numbers
    1. 'New Statement for the [Included] Field ==> Included: fCalcIncluded([Percentile])
  4. Here is what is happening. For each Grouping Level, the Value of the [Percentile] Field is passed to the fCalcIncluded() Function. The Percentile is analyzed, and if the Value is <.75 the Function returns 'Yes'. If Percentile is >=.75 and it is the 1st occurrence where Percentile >=.75, then the Functions returns 'Yes', otherwise 'No'.
  5. You 'must' Reset the Value of the Public Variable (intNumOfTypeOvers) to 0 before you execute the Query, as in:
    Expand|Select|Wrap|Line Numbers
    1. intNumOfTypeOvers = 0
    2. DoCmd.OpenQuery "qryClaims", acViewNormal
  6. Amazingly, this all seems to work quite well. (LOL).
  7. I won't even attempt to tackle the 2nd dilemma (ties at the Cutoff Point) until I am assured that I am on the right track. Only you can tell me that.

16 1642
Is this even possible in Access or is it too complex?
Aug 6 '10 #2
ADezii
8,834 Expert 8TB
Subscribing for now, gotta think it over.
Aug 6 '10 #3
NeoPa
32,556 Expert Mod 16PB
JDFergus: 80952144 is the total sum of all claims (entering Sum(Claims) only gives the sum for each "Type").
Let's start with this. You may find another DSum() call would work for you here (Sum() would behave exactly as you describe) :
Expand|Select|Wrap|Line Numbers
  1. Percentile: [RunTotal]/DSum('[Claims]', '[ClaimsType]')
Aug 6 '10 #4
NeoPa
32,556 Expert Mod 16PB
What does the data look like in [ClaimsType]? Is it already grouped by [Type]? Or is that done within your query?
Aug 6 '10 #5
ADezii
8,834 Expert 8TB
Kindly Post the actual SQL for the Query.
Aug 6 '10 #6
Expand|Select|Wrap|Line Numbers
  1. SELECT ClaimsType.ClaimsType, ClaimsType.Claims, DSum("Claims","ClaimsType","[Claims] >= " & [Claims]) AS RunTotal, [RunTotal]/DSum('Claims','ClaimsType') AS Percentile, IIf([Percentile]<0.75,"Yes","No") AS Included
  2. FROM ClaimsType
  3. GROUP BY ClaimsType.ClaimsType, ClaimsType.Claims
  4. ORDER BY ClaimsType.Claims DESC;
  5.  
Thank you for fixing my DSum problem, NeoPa. I knew that wasn't correct but wasn't exactly sure how to fix it. I've corrected it in the above SQL. Also, "ClaimsType" is just the text for the primary key of claim, such as 'A010'. It's already grouped by that so there's only one "ClaimsType" for each set of claims.
Aug 6 '10 #7
NeoPa
32,556 Expert Mod 16PB
I'd really like to see some example data from [ClaimsType] if you don't mind :)

I'm pleased to have helped with the DSum() thing.

PS. Don't mind me. I'm just going to repost your SQL in such a way as to make it a little easier to work with :
Expand|Select|Wrap|Line Numbers
  1. SELECT   [ClaimsType]
  2.        , [Claims]
  3.        , DSum('Claims','ClaimsType','[Claims]>=' & [Claims]) AS RunTotal
  4.        , [RunTotal]/DSum('Claims','ClaimsType') AS Percentile
  5.        , IIf([Percentile]<0.75,'Yes','No') AS Included
  6. FROM     [ClaimsType]
  7. GROUP BY [ClaimsType]
  8.        , [Claims]
  9. ORDER BY [Claims] DESC
Aug 6 '10 #8
ClaimsType Claims RunTotal Percentile Included
25000 1582232 1582232 1.9545% Yes
4019 1168548 2750780 3.3963% Yes
2724 1115988 3866768 4.7766% Yes
5856 1099347 4966115 6.1346% Yes
78650 1063707 6029822 7.4486% Yes

Here are exactly the first 5 records of what the query would produce now. "ClaimsType" is text. If you're worried about the naming, I changed it from what it actually is before posting on here for security/simplicity reasons.
Aug 6 '10 #9
NeoPa
32,556 Expert Mod 16PB
All information is likely to be helpful JD, but I asked for some example data from the [ClaimsType] table. I need to get a clearer understanding of what we are working with.
Aug 6 '10 #10
ADezii
8,834 Expert 8TB
I do believe that I have a solution for at least your first dilemma, namely have the Included Field show 'Yes' for the 1st additional 'Type' over .75 Percentile.
  1. Declare a Public Variable as follows in a Standard Code Module:
    Expand|Select|Wrap|Line Numbers
    1. Public intNumOfTypeOvers As Integer
  2. Copy-N-Paste the following Public Function into a Standard Code Module:
    Expand|Select|Wrap|Line Numbers
    1. Public Function fCalcIncluded(sngPercentile As Single) As String
    2. 'intNumOfTypeOvers must retain Value between successive Calls
    3. If sngPercentile < 0.75 Then
    4.   fCalcIncluded = "Yes"
    5. Else
    6.   intNumOfTypeOvers = intNumOfTypeOvers + 1
    7.     If intNumOfTypeOvers = 1 Then   'and only 1
    8.       fCalcIncluded = "Yes"
    9.     Else
    10.       fCalcIncluded = "No"
    11.     End If
    12. End If
    13. End Function
  3. Change the manner in which the Calculated Field [Included] obtains its Value, namely:
    Expand|Select|Wrap|Line Numbers
    1. 'Outta here! ==> Included: IIf([Percentile]<0.75,"Yes","No")
    Expand|Select|Wrap|Line Numbers
    1. 'New Statement for the [Included] Field ==> Included: fCalcIncluded([Percentile])
  4. Here is what is happening. For each Grouping Level, the Value of the [Percentile] Field is passed to the fCalcIncluded() Function. The Percentile is analyzed, and if the Value is <.75 the Function returns 'Yes'. If Percentile is >=.75 and it is the 1st occurrence where Percentile >=.75, then the Functions returns 'Yes', otherwise 'No'.
  5. You 'must' Reset the Value of the Public Variable (intNumOfTypeOvers) to 0 before you execute the Query, as in:
    Expand|Select|Wrap|Line Numbers
    1. intNumOfTypeOvers = 0
    2. DoCmd.OpenQuery "qryClaims", acViewNormal
  6. Amazingly, this all seems to work quite well. (LOL).
  7. I won't even attempt to tackle the 2nd dilemma (ties at the Cutoff Point) until I am assured that I am on the right track. Only you can tell me that.
Aug 6 '10 #11
Thanks for the help so far, guys.

Sorry NeoPa, I thought you meant you were asking for example data from the field "ClaimsType", not the table. But the first two fields in my example query (ClaimsType and Claims) are the only two fields in the table. Unfortunately there's no other information relating to this table yet.

And I tried your code, ADezii, and I think it worked the first time, but since it works the same as mine. I'm pretty sure it has to do with step 5 though, where exactly does that code supposed to go? I get errors if I try to include it in the other module or if I give it its own. Forgive me for my complete grasp (or lack thereof) of VBA and code modules.
Aug 6 '10 #12
ADezii
8,834 Expert 8TB
The two critical points to mention are:
  1. The Function must be 'Public' and reside in a Standard Code Module.
  2. The Value of the Public Variable 'must' be Reset prior to Opening the Query after its initial execution.
  3. The following code can reside in various places, but the most obvious location would be the Click() Event of a Command Button:
    Expand|Select|Wrap|Line Numbers
    1. intNumOfTypeOvers = 0       'RESET 
    2. DoCmd.OpenQuery "qryClaims", acViewNormal 
  4. The fact that it worked only the 'first' time is due to the Public Variable (intNumOfTypeOvers) probably has not been Reset as indicated above.
Aug 6 '10 #13
I figured how why it only worked once. The first time I went straight to the record I knew where the cutoff point was (by entering the row number at the bottom of the query), and it showed "Yes" for it then. The other times I scrolled down to the record and it wasn't. And actually, the first time it was right when I moved there, but as soon as I scrolled up or down the record changed.

I think this is because the formula has to recalculate everytime the records scroll down, but not when the exact row is entered. (You'll notice the field is empty for a breif second each time before it has to repopulate as you scroll down.)

So unfortunately this would mean that the query would still be changing even after I click a command button, and would not be realistic to use.
Aug 6 '10 #14
NeoPa
32,556 Expert Mod 16PB
jdfergus: Sorry NeoPa, I thought you meant you were asking for example data from the field "ClaimsType", not the table. But the first two fields in my example query (ClaimsType and Claims) are the only two fields in the table. Unfortunately there's no other information relating to this table yet.
While I don't argue with anything you say, I still need to see the data for myself. I cannot ask you to tell me the important bit of the puzzle you've missed out, because you'll certainly not know what that is (otherwise I'm sure you would have mentioned it already). Therefore I ask for data that will help me to ascertain that without relying on you to describe it or interfere with it in any way.

I just want to see what the data is (or a representative subset at least if the whole is too voluminous) that you desire to build your results from.

As for ADezii's solution...
I was going to comment on this earlier, but I was waiting for some hard data so that I could be sure of my facts first. It's actually a pretty clever way of producing the result you want. If it's possible to do in SQL (still unclear until data available, but I'm guessing not) then it is almost certainly a better option for two reasons :
  1. Performance. Calling VBA functions which need to be re-interpreted at every use can severely impact on the performance of queries running on large data sets.
    BTW. From what I could see ADezii got the algorithm spot on.
  2. Retreadability. This is what you are falling over at the moment. The fundamental logic of the algorithm depends on (assumes) the fact that the data will be processed forwards, in order, once only. Moving backwards and forwards through the data is not allowed for.
    There are circumstances where this can be relied upon, but it seems you are not running under those circumstances.
    This is actually quite unfortunate, as this may well be your best option. When I have the data I will certainly apply some thought to the matter, but I'm frankly not hopeful at this stage, as this problem is fundamentally about relativeness of records. A concept that SQL is fundamentally blind to - hence the need for VBA processing of the SQL returned records.
Aug 6 '10 #15
Still not sure what other "data" you could be looking for, as I gave an example of the first 5 records and said the only 2 fields and data types in the table. I couldn't even say that much more even if I wanted to because of security reasons.

But I think you're right anyway, that relativeness of records is something that is not easily done in SQL, which is what I was afraid of. The actual process itself is done anyway, I was just wondering if there was an easier way to do it in SQL for documentation and repeatability purposes, as opposed to manually importing and exporting the data into Excel for it to handle like I originally did. But apparently there's not, lol.

So I'll consider this case closed, but thanks again for the help, though.
Aug 9 '10 #16
NeoPa
32,556 Expert Mod 16PB
Good enough JD :)
Aug 9 '10 #17

Sign in to post your reply or Sign up for a free account.

Similar topics

2
by: C L Humphreys | last post by:
Hi, Using a soundex function that works with anything except an empty/null string the following gives me an error part way through the results select surname, soundex(surname) from pd where...
3
by: William Wisnieski | last post by:
Hello Everyone, Access 2000, I have a main unbound form with a bound datasheet subform . The subform is bound to a query that returns records based on criteria in the main form. The user...
1
by: Craig M | last post by:
Hi, I've created a query that has all calculations needed, and made a report based on this. I have then made a form based on this query. What I would like to do is print only the current...
3
by: Adam Sandler | last post by:
Hello, This problem came up during testing. There's a button on the page and when a user selects the button, a row is added to the database. If the user refreshes the browser (View | Refresh...
4
by: tweeterbot | last post by:
Hi, I am a chemical engineer trying to design a database and I am running into some trouble. My database is going to be 'processing' raw data to get the figures we need to prepare the monthly...
1
by: jignesh thanki | last post by:
how insert record through query in access
4
by: mashimaro | last post by:
Hi! I want to ask how can I get the record from the query. I currently have a form with 5 comboboxes and a button. After the user fills those 5 comboboxes and hit the button, a new form is opened...
1
by: HH | last post by:
To append a single record, without getting a warning if a record already exists, I found out the following syntax works fine as Query in MS Access: first create a simple 'products' table... ...
5
by: artemetis | last post by:
Hello! It's me again. tblEmp -->empUid - self explanatory -->empPerf - ranking from 0 - 100 I'm interested in creating a query that displays the top x% (eg, 20%) of employee performance,...
5
by: Poweruser | last post by:
Another question I've always asked myself: Is there any possibility to do some sort of "for each record in query result A do some VBA code (e.g. call a VBA procedure)? Best regards, D.R.
0
by: DolphinDB | last post by:
Tired of spending countless mintues downsampling your data? Look no further! In this article, you’ll learn how to efficiently downsample 6.48 billion high-frequency records to 61 million...
0
by: ryjfgjl | last post by:
ExcelToDatabase: batch import excel into database automatically...
0
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
0
by: jfyes | last post by:
As a hardware engineer, after seeing that CEIWEI recently released a new tool for Modbus RTU Over TCP/UDP filtering and monitoring, I actively went to its official website to take a look. It turned...
0
by: ArrayDB | last post by:
The error message I've encountered is; ERROR:root:Error generating model response: exception: access violation writing 0x0000000000005140, which seems to be indicative of an access violation...
1
by: PapaRatzi | last post by:
Hello, I am teaching myself MS Access forms design and Visual Basic. I've created a table to capture a list of Top 30 singles and forms to capture new entries. The final step is a form (unbound)...
1
by: CloudSolutions | last post by:
Introduction: For many beginners and individual users, requiring a credit card and email registration may pose a barrier when starting to use cloud servers. However, some cloud server providers now...
1
by: Defcon1945 | last post by:
I'm trying to learn Python using Pycharm but import shutil doesn't work
0
by: af34tf | last post by:
Hi Guys, I have a domain whose name is BytesLimited.com, and I want to sell it. Does anyone know about platforms that allow me to list my domain in auction for free. Thank you

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.