percent query rounding less than .5 up

[jtmo3]

I have a database that holds scores for shooters. I am trying to get a report that breaks down certain shooters who score above a certain amount. I am taking the shooters who score above a certain amount in each class and using a select top 1 query, pulling out the top shooter and awarding them 10 points. No problem. The issue comes in when I take the remainder of those shooters who shot above a certain score. I then need to take the top 15% of the remaining shooters and award them 7 points. Problem is, if I use Select Top 15 percent in the sql statement of the access query, if the total number of shooters is less than .5 (ex 3 shooters remain. 3 x .15 is .45), access is pulling in one shooter. It seems to be rounding up if there is anything in the record rather than less than .5 rounding down to 0, which is what I need it to do. If the amount remaining is less than .5 I need it to drop the record and award no one. But it is pulling in one record.



What am I missing here? Why is access not rounding down less than .5 to 0?

Thanks.

John

[June7]

I don't know what rounding rules the TOP x% follows, but apparently doesn't want to return a 0. Probably has to do with the nature of the TOP keyword, something has to be at the top, unless there is a WHERE clause that causes no records to be considered.

Other notes about rounding.

VBA Round function uses an even/odd rule (bankers rounding).
Round(1.345) = 1.34
Round(1.375) = 1.38

VBA Format function does not use even/odd, nor does Round in Excel cell forumlas.

[jtmo3]

Thanks for the reply. I ran a test query in access. I pulled some records from the tables with a query. I pulled 42 records without any top statements. If I tell it select top 50 percent, it pulls in 21 records. If I tell it 25 percent, it pulls in 11. If I tell it 10 percent, it pulls in 5 records (42*.10 is 4.2. Why pull in 5) If I tell it to pull 9 percent, it pulls in 4 records (42*.09 is 3.78. It rounds up ok).

Any ideas what's going on or how I can get around this? Thanks again.

[June7]

It would appear the rule is to always round up. As it is not possible to retrieve less than an entire record (as opposed to eating part of a pie), Access/SQL plays it safe and considers any part as a whole.

The only way I could see to handle this is with VBA code function.