Solved

Top25, Middle50, Bottom25

Posted on 1998-08-20
5
150 Views
Last Modified: 2010-04-30
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
Comment
Question by:steambyte
  • 2
  • 2
5 Comments
 
LVL 1

Expert Comment

by:wford
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

by:
rlarner earned 200 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

by:steambyte
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

by:rlarner
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

by:steambyte
ID: 1429979
Thanks I will try that
0

Featured Post

Top 6 Sources for Identifying Threat Actor TTPs

Understanding your enemy is essential. These six sources will help you identify the most popular threat actor tactics, techniques, and procedures (TTPs).

Join & Write a Comment

Suggested Solutions

Introduction I needed to skip over some file processing within a For...Next loop in some old production code and wished that VB (classic) had a statement that would drop down to the end of the current iteration, bypassing the statements that were c…
Introduction While answering a recent question about filtering a custom class collection, I realized that this could be accomplished with very little code by using the ScriptControl (SC) library.  This article will introduce you to the SC library a…
Get people started with the process of using Access VBA to control Outlook using automation, Microsoft Access can control other applications. An example is the ability to programmatically talk to Microsoft Outlook. Using automation, an Access applic…
Get people started with the process of using Access VBA to control Excel using automation, Microsoft Access can control other applications. An example is the ability to programmatically talk to Excel. Using automation, an Access application can laun…

707 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

12 Experts available now in Live!

Get 1:1 Help Now