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.

 

[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!!!