Getting Past Integer Only Requirement for Top XX PERCENT

[mcl]

Is there a way to get Access 2003 to get past the whole number limit for top
percent?

I'm crunching a large amount of weather data for a particular station (I'm a
meteorologist).

There are as many as 5000 records per month with data going back to 1943. I
need to pull the

lowest 95.95% value for each month. I can use top 99 percent to get the 99%
value.

I select the values by month, sort descending and taking the top 99%. Then
use

that query as input to another to select the lowest value.

But can I somehow get 99.95%. Note that I don't know VBA.

So if you give me VBA code you need to be very specific.

 

[Neil Sunderland]

Is there a way to get Access 2003 to get past the whole number limit for top
percent?

Not directly, no. But...

I'm crunching a large amount of weather data for a particular station (I'm
a meteorologist).

There are as many as 5000 records per month with data going back to
1943. I need to pull the lowest 95.95% value for each month. I can use
top 99 percent to get the 99% value.

I select the values by month, sort descending and taking the top 99%.
Then use that query as input to another to select the lowest value.

In theory, you should be able to do this with a Ranking query.

Something like this would do it:
[qryRankedObservations]
SELECT O1.obs_date, FORMAT(O1.obs_date, "mmmyyyy") AS obs_month,
O1.obs_rainfall,
(SELECT COUNT(*) FROM Observations AS O2
WHERE O1.obs_rainfall >= O2.obs_rainfall AND
FORMAT(O1.obs_date, "mmmyyyy") =
FORMAT(O2.obs_date, "mmmyyyy")) AS Rank
FROM observations AS O1

You can then create a second query that would calculate your 99.85%
point for each month:
[qryPickObservation]
SELECT obs_month,
INT((MAX(Rank))*0.9985) AS record_nbr
FROM qryRankedObservations
GROUP BY obs_month

Then this query should pull out that particular value for each month:

SELECT R.obs_date, R.obs_month, R.obs_rainfall
FROM qryRankedObservations AS R
INNER JOIN qryPickObservation AS P
ON P.obs_month = R.obs_month
WHERE R.Rank = P.record_nbr

I did say 'in theory': given that you have 5000 rows per month going
back 60 years, this could (or should, or will) run exceedingly slowly!