Solved

Top25, Middle50, Bottom25

Posted on 1998-08-20
5
157 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
[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
  • Learn & ask questions
  • 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

Salesforce Made Easy to Use

On-screen guidance at the moment of need enables you & your employees to focus on the core, you can now boost your adoption rates swiftly and simply with one easy tool.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
using web browser with BING 40 145
Visual Studio 2005 text editor 10 54
How to read File Date Created using VB6 8 70
Modify Text File with Excel Macro 13 39
Introduction While answering a recent question (http://www.experts-exchange.com/Q_27402310.html) in the VB classic zone, I wrote some VB code in the (Office) VBA environment, rather than fire up my older PC.  I didn't post completely correct code o…
Have you ever wanted to restrict the users input in a textbox to numbers, and while doing that make sure that they can't 'cheat' by pasting in non-numeric text? Of course you can do that with code you write yourself but it's tedious and error-prone …
Get people started with the utilization of class modules. Class modules can be a powerful tool in Microsoft Access. They allow you to create self-contained objects that encapsulate functionality. They can easily hide the complexity of a process from…
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…

740 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