find the top 8 values in recordset, done in code
Stephane --
Let's make sure I understand your task. You are looking for 16 kmh
ranges (0-15,16-31,32-47,...); you want the top of the range which is higher
than the speed of 85% of the vehicles. I don't see how your "8 largest
volumes of vehicles" algorithm can gurantee that. Here's a set of queries
that should work (but untested).
First create a summary table containing counts by 16 kmh range
SELECT 16*Int(speed/16)+15 as range, count(*) as nveh
INTO sumtab
FROM <source>
GROUP BY 16*int(speed/16)+15
Then create a totals table so we know how many vehicles there are
SELECT sum(nveh) as tveh
INTO tottab
FROM sumtab
Then create a cums table so we know how many vehicles are going at a
range or slower
SELECT s1.range,sum(s2.nveh) as nveh
INTO cumtab
FROM sumtab as s1 JOIN sumtab as s2 on s1.range>=s2.range
GROUP BY s1.range
Then select the lowest range containing 85% of the vehicles
SELECT min(cumtab.range)
FROM cumtab JOIN tottab on cumtab.nveh<=tottab.tveh
WHERE cumtab.nveh>=.85*tottab.tveh
HTH, Roger
Quote:
>What would be the best way to write code to find the top 8 values in a
>recordset. I am currently using a Top 8 query but it is not returning the
>correct values. The top query does not distinguish between matching values,
>and therefore skews the results. I guess I need to rank the values?...
>the objective is to find the top speed of a 16 km
>h range where 85% of vehicules are moving. You do this by finding the 8
>largest volumes of vehicules and returning the top most value of that speed
>range ex: 80 to 30 km/h, top range is 80.
>Does anyone have code to do this?
>--
>Thanks,
>Stephane Viau
>Ottawa, Canada