ADezii,
i have been reading your solution to this percentile problem and believe you can help me.
I have a similar problem with creating a percentile in access 2007.
My table is called "TBL_DATA" and the field with all the values i would like to gain a percentile for is called "DAYS"
i want to develop a query which shows the following columns
- Min Days
- Max days
- Avg Days
- Percentile 95th
- Percentile 75th
- Percentile 50th
- Percentile 25th
i have created the first 3 columns in design view however am having problems creating the percentile calculations.
I would like access to look at all values under "DAYS" and bring back the percentile requested.
I am happyusing either a VBA script or a SQL statement as long as it gets the right answer.
Hope you can help, Thanking you in advance.
19 11162 ADezii 8,834
Recognized Expert Expert @SachinPatel - Copy and Paste the following 'Public' Function to a Standard Code Module:
- Public Function fCalculatePercentiles(sngPercentile As Single)
-
'Make sure to 1st set a Reference to the
-
'Microsoft Excel XX.X Object Library
-
Dim sngNumbers() As Single
-
Dim intNumberOfRecords As Integer
-
Dim objExcel As Excel.Application
-
Dim intCounter As Integer
-
Dim MyDB As DAO.Database
-
Dim rstPercentile As DAO.Recordset
-
-
Set MyDB = CurrentDb()
-
Set rstPercentile = MyDB.OpenRecordset("TBL_DATA", dbOpenSnapshot)
-
rstPercentile.MoveLast: rstPercentile.MoveFirst 'accurate Record Count
-
-
intNumberOfRecords = rstPercentile.RecordCount
-
-
'Redimension the Array to proper values
-
ReDim sngNumbers(1 To intNumberOfRecords)
-
-
Set objExcel = CreateObject("Excel.Application")
-
-
'Populate the Array with values from the Recordset
-
For intCounter = 1 To intNumberOfRecords
-
With rstPercentile
-
sngNumbers(intCounter) = ![Days]
-
.MoveNext
-
End With
-
Next
-
-
'Pass the Array to Excel's Percentile Function and Round the result to 2 Decimal Places
-
fCalculatePercentiles = Round(objExcel.Application.Percentile(sngNumbers(), sngPercentile), 2)
-
-
'Don't forget to clean up!!!
-
rstPercentile.Close
-
Set rstPercentile = Nothing
-
objExcel.Quit
-
Set objExcel = Nothing
-
End Function
- Execute the following SQL Statement given your Table and Field Names:
- SELECT Min(TBL_DATA.Days) AS MinOfDays, Max(TBL_DATA.Days) AS MaxOfDays, Avg(TBL_DATA.Days) AS AvgOfDays,
-
fCalculatePercentiles(0.95) AS [95th Percentile],
-
fCalculatePercentiles(0.75) AS [75th Percentile],
-
fCalculatePercentiles(0.5) AS [50th Percentile],
-
fCalculatePercentiles(0.25) AS [25th Percentile]
-
FROM TBL_DATA
-
GROUP BY fCalculatePercentiles(0.25);
- Note that you are passing the actual Percentile Value to the fCalculatePerce ntiles() Function within the SQL Statement.
- Good Luck and Good Night!
ADezil,
sorry to bother you buti have doneas you stated above however when i try run the query i am getting the error message as follows:
Undefined Function 'fCalculatePerc entiles' in expression
is there a certain way i shouldbe saving the VBA code to ensure the query can refer to it?
ADezii 8,834
Recognized Expert Expert @SachinPatel
As previously stated in Post #30, Item 1, it must be defined as ' Public' in a Standard Code Module.
Sorry mate - i believe i am defining the item as public, however as this is new to me maybe i am doing it wrong- can you possible tell me the correct way to define the function as public in a standard code module?
ADezii 8,834
Recognized Expert Expert @SachinPatel
I'll do better than that. I'll be in work all day, but when I get home this evening I'll send you an Attachment indicating how, exactly, this is done. Be advised that this has only been tested on an insignificant amount of data, and may be incredibly slow on a large number of Records since you are using 3 Aggregate Functions, as well as several Calls to the same Public Function with different Parameters, within the Query. You may also experience some difficulty since you are essentially making the same Function Calls by passing different values (Percentiles). All the good stuff I'll leave to you - see you later. OK, was able to get it done here.
thanks mate your a life saver - i will log on later (5 hours or so)
thanks again your a star!
ADezii 8,834
Recognized Expert Expert @SachinPatel
Just in case you didn't notice the previous Post, I was able to create the Attachment in work. Good Luck.
NeoPa 32,568
Recognized Expert Moderator MVP
I have split this away from Percentile calculation in Access as it is a different question.
I know you're very new so we'll let it slide this time, but posting your question in an existing thread is hijacking, and is not allowed on this site.
I don't, for a moment, want to stop you asking questions, simply guiding you do do it the proper way in future.
Welcome to Bytes!
NB. Post #6 has the attachment ADezii promised you. Don't overlook it simply because it's not the latest post ;)
hey ADezii,
just seeing if you had chance to send over how to make a vba function public and refer toit on the sql you created without getting an error message.
Thanks
Sign in to post your reply or Sign up for a free account.
Similar topics |
by: tomvr |
last post by:
Hello
I have noticed some 'weird' memory usage in a vb.net windows app
The situation is as follows
I have an app (heavy on images) with 2 forms
(actually there are more forms and on starting the app I load some things into memory for global use of the app but I'll use only 2 starting forms to explain the situation)
situation 1
start app with form 1 (72mb memory usage), show form 2 and hide form 1 (89 mb memory usage
|
by: Ian Taite |
last post by:
Hello,
I'm exploring why one of my C# .NET apps has "high" memory usage, and
whether I can reduce the memory usage.
I have an app that wakes up and processes text files into a database
periodically. What happens, is that the app reads the contents of a
text file line by line into an ArrayList. Each element of the ArrayList
is a string representing a record from the file. The ArrayList is then
processed, and the arraylist goes out of...
|
by: jd142 |
last post by:
Normally when people ask about getting the Nth percentile from a group,
the response is to write a couple of VBA modules. But it seems to me
there's a simpler way that just uses 2 queries. You might even be able
to get it down to one query if you nest them.
Here's how I get the 25th percentile number from a table, where
mynumber is the field that has the value and mytable is the table name:
top_25_query:
select top 25 percent...
|
by: Dave Veeneman |
last post by:
I'm looking from an algorithm that will do a statistics calculation for me--
it's the first time 'Numerical Recipies in C' has failed me! A normal
distribution table tells me the probability (by percentile) of an item,
given something called a z-score. If I have a z-score, I can look up the
probability in a distribution table.
There are plenty of algorithms around to do that calculation, but I need to
do a reverse lookup. I am given a...
|
by: Joe |
last post by:
Has anyone written a percentile rank algorithm that they can share or
know of a good resource that defines the algorithm. I'm starting a
project that involves percentile ranks, logistic and linear regression.
If not c# any language or pseudo code would be very useful. Thank you.
Joe
--
Sent via .NET Newsgroups
http://www.dotnetnewsgroups.com
| |
by: rdemyan via AccessMonster.com |
last post by:
My app contains utility meter usage. One of the things we have to deal with
is when a usage is clearly incorrect. Perhaps someone wrote the meter
reading down incorrectly or made a factor of 10 error when entering the
reading, etc. At other times the usage is zero or somehow was entered as a
negative number.
So I'm thinking about adding functionality to search for such anomalies. For
instance, show months where the meter reading is...
|
by: Sirisha |
last post by:
I am using the following code to get the CPU usage
PerformanceCounter myCounter;
myCounter = new PerformanceCounter();
myCounter.CategoryName = "Processor";
myCounter.CounterName = "% Processor Time";
myCounter.InstanceName = "_Total";
for(int i=0; i < 20; i++)
|
by: riaane |
last post by:
Hey clever people, Excel has the =PERCENTILE function. This calulates a percentile value from an array of values. Is there any equivalent in Access?
|
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, based on empPerf. Qry operator will be prompted for value of X%.
I'm not too familiar with stats, but searched through the site and found l was only able to find something regarding Excel.
|
by: marktang |
last post by:
ONU (Optical Network Unit) is one of the key components for providing high-speed Internet services. Its primary function is to act as an endpoint device located at the user's premises. However, people are often confused as to whether an ONU can Work As a Router. In this blog post, we’ll explore What is ONU, What Is Router, ONU & Router’s main usage, and What is the difference between ONU and Router. Let’s take a closer look !
Part I. Meaning of...
|
by: Hystou |
last post by:
Most computers default to English, but sometimes we require a different language, especially when relocating. Forgot to request a specific language before your computer shipped? No problem! You can effortlessly switch the default language on Windows 10 without reinstalling. I'll walk you through it.
First, let's disable language synchronization. With a Microsoft account, language settings sync across devices. To prevent any complications,...
| |
by: jinu1996 |
last post by:
In today's digital age, having a compelling online presence is paramount for businesses aiming to thrive in a competitive landscape. At the heart of this digital strategy lies an intricately woven tapestry of website design and digital marketing. It's not merely about having a website; it's about crafting an immersive digital experience that captivates audiences and drives business growth.
The Art of Business Website Design
Your website is...
|
by: Hystou |
last post by:
Overview:
Windows 11 and 10 have less user interface control over operating system update behaviour than previous versions of Windows. In Windows 11 and 10, there is no way to turn off the Windows Update option using the Control Panel or Settings app; it automatically checks for updates and installs any it finds, whether you like it or not. For most users, this new feature is actually very convenient. If you want to control the update process,...
|
by: tracyyun |
last post by:
Dear forum friends,
With the development of smart home technology, a variety of wireless communication protocols have appeared on the market, such as Zigbee, Z-Wave, Wi-Fi, Bluetooth, etc. Each protocol has its own unique characteristics and advantages, but as a user who is planning to build a smart home system, I am a bit confused by the choice of these technologies. I'm particularly interested in Zigbee because I've heard it does some...
|
by: isladogs |
last post by:
The next Access Europe User Group meeting will be on Wednesday 1 May 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM).
In this session, we are pleased to welcome a new presenter, Adolph Dupré who will be discussing some powerful techniques for using class modules.
He will explain when you may want to use classes instead of User Defined Types (UDT). For example, to manage the data in unbound forms.
Adolph will...
|
by: TSSRALBI |
last post by:
Hello
I'm a network technician in training and I need your help.
I am currently learning how to create and manage the different types of VPNs and I have a question about LAN-to-LAN VPNs.
The last exercise I practiced was to create a LAN-to-LAN VPN between two Pfsense firewalls, by using IPSEC protocols.
I succeeded, with both firewalls in the same network. But I'm wondering if it's possible to do the same thing, with 2 Pfsense firewalls...
|
by: adsilva |
last post by:
A Windows Forms form does not have the event Unload, like VB6. What one acts like?
| |
by: bsmnconsultancy |
last post by:
In today's digital era, a well-designed website is crucial for businesses looking to succeed. Whether you're a small business owner or a large corporation in Toronto, having a strong online presence can significantly impact your brand's success. BSMN Consultancy, a leader in Website Development in Toronto offers valuable insights into creating effective websites that not only look great but also perform exceptionally well. In this comprehensive...
| |