Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
Solved

# Top25, Middle50, Bottom25

Posted on 1998-08-20
Medium Priority
162 Views
I am writing a program that analyses the top 25%, bottom 25%
of a range of data.
Two part question.
1.
This is no problem but I need the average of these, not all the records.
I can do this by getting the top25/bottom25 using a query and running through recordset and then dividing by the number in each.
However I thought it may be more efficient to use a direct query, such as;

dim rs a recordset
dim rs2 a recordset

set rs=db.openrecordset("SELECT TOP 25 PERCENT FROM sometable ORDER BY somefield")

set rs2=db.openrecordset("SELECT Avg(somefield) AS avevalue
FROM " & rs2.recordset)

The rs2 causes a type mismatch error of course. Is there a way around this.

2.
How could I calculate the average of the MIDDLE 50%
0
Question by:steambyte
[X]
###### Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

• Help others & share knowledge
• Earn cash & points
• 2
• 2

LVL 1

Expert Comment

ID: 1429975
I'm not sure of the 1st one, but for 2.
if you can find the total ave then:

ave50 = 2ave - 0.5ave25T - 0.5ave25B

0

LVL 1

Accepted Solution

rlarner earned 600 total points
ID: 1429976
Well, as you noticed, you can't really join recordsets.  I would recomend: having 3 recordsets (all, top25, bottom25), each of which do NOT return the average.  Instead, return the total (of the field you are trying to average) and the count.  Then, you should be able to use those numbers to calculate all the averages you want:
top25avg= top25.total / top25.count
bottom25avg = bottom25.total / bottom25.count
middle50 = (total.total - top25.total - bottom25.total) / (total.count - top25.count - bottom25.count)
or something like that.
0

Author Comment

ID: 1429977
Is it possible to query the [all] recordset rather having to hit the database each time. My problem is that the report that these are going into has 33 values that each have to have their own value and then the TOP25,MIDDLE25, BOTTOM25 to compare against.

0

LVL 1

Expert Comment

ID: 1429978
Hmm... I can't think of any real good ways to get that info.  (There are some very complex queries that might do what you want, but they would probably slow down the process so much that it wouldn't be worth it.  I'm thiking of something like outer-joining the table against itself 3 times, with the 2nd and third times filtered to only the top25 and bottom25 values.)

If those 33 values that you are going to compare averages for are all in the same table, then you still need only need 3 queries.

If you aren't doing anything that requires you to build this query at runtime, it might be possible to design a query (or more likely several queries, one using the others) and save it that would gather all the values you need into one spot.  This isn't any less database intensive, but it would give you one place to go.  (ie create and save queries that will give you the top25avg, bottom25avg, and middle50avg, and then create a query that includes all three queries and displays all of their records)
0

Author Comment

ID: 1429979
Thanks I will try that
0

## Featured Post

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Introduction In a recent article (http://www.experts-exchange.com/A_7811-A-Better-Concatenate-Function.html) for the Excel community, I showed an improved version of the Excel Concatenate() function.  While writing that article I realized that no o…
Most everyone who has done any programming in VB6 knows that you can do something in code like Debug.Print MyVar and that when the program runs from the IDE, the value of MyVar will be displayed in the Immediate Window. Less well known is Debug.Asse…
As developers, we are not limited to the functions provided by the VBA language. In addition, we can call the functions that are part of the Windows operating system. These functions are part of the Windows API (Application Programming Interface). U…
This lesson covers basic error handling code in Microsoft Excel using VBA. This is the first lesson in a 3-part series that uses code to loop through an Excel spreadsheet in VBA and then fix errors, taking advantage of error handling code. This l…
###### Suggested Courses
Course of the Month11 days, 19 hours left to enroll