Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
• Status: Solved
• Priority: Medium
• Security: Public
• Views: 164

# Top25, Middle50, Bottom25

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
steambyte
• 2
• 2
1 Solution

Commented:
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

Commented:
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 Commented:
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

Commented:
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 Commented:
Thanks I will try that
0

## Featured Post

• 2
• 2
Tackle projects and never again get stuck behind a technical roadblock.