Dropping off outlying data for meaningful interval

[vangogh228]

Each day, I receive a set of data (of varying size) which is, essentially, a simple set of timings in seconds with other appropriate identfying data (source, date, person, etc). One set of the calculations I do is mean, min and max. However, it has been requested that a reasonable interval be determined to make the data more meaningful. We have decided to drop off the top 8% and bottom 2% of the data prior to making the calculation.

Trouble is, I have no idea how to do this other than manually.

Any help is GREATLY appreciated.

Tom

 

[yhgtbfk]

Hmm...Ok, try this

Have a number field identifying each record, which is unique (ie- auto number).

Then do something like

'This gets the total number of rows
rowsNumber = dlookup("count(number)", "myTable")

'This calculates how many rows makes up 8%
the8 = rowsNumber * .08
'This calculates how many rows makes up 2%
the2 = rowsNumber * .02

docmd.setwarnings false
'While the bottom 8% is one row or more
Do While the8 > 0
'Get the first record (which has the smallest number)
getRow = Dlookup("min(number)", "myTable")
'Delete it
docmd.runsql ("delete from myTable WHERE number = '" & getRow & "'")
'Subtract one from the counter
the8 = the8 - 1

Loop

Do While the2 > 0

getRow = Dlookup("max(number)", "myTable")

docmd.runsql ("delete from myTable WHERE number = '" & getRow & "'")

the2 = the2 - 1

Loop

docmd.setwarnings true

I hope that helps, or atleast makes sense.

 

[Pat Hartman]

The TOP predicate can be a count or a percent. So:

SELECT TOP 8 PERCENT fld1, fld2, fld3, etc. Will select the top 8%. You will need to use an order by clause to make this work the way you want. Then a second query can select the TOP 2% but in this case you want the order by to be descending. Union these two queries and finally create an "unmatched" query using the wizard that matches the table to the union query. The records returned will be those not selected by either Top values query.

 

[vangogh228]

Pat: THANKS!

I was going to try to implement the first solution, but quickly gave up. I see where you're headed, and feel comfortable I can successfully make it happen. In the meantime, I found I can do this fairly easily in Excel, but I don't want to have to export/import data to get this done. Thanks again!!!