?
Solved

Top25, Middle50, Bottom25

Posted on 1998-08-20
5
Medium Priority
?
160 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 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

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

What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

Question has a verified solution.

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

Enums (shorthand for ‘enumerations’) are not often used by programmers but they can be quite valuable when they are.  What are they? An Enum is just a type of variable like a string or an Integer, but in this case one that you create that contains…
This article describes some techniques which will make your VBA or Visual Basic Classic code easier to understand and maintain, whether by you, your replacement, or another Experts-Exchange expert.
Show developers how to use a criteria form to limit the data that appears on an Access report. It is a common requirement that users can specify the criteria for a report at runtime. The easiest way to accomplish this is using a criteria form that a…
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, 20 hours left to enroll

752 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