NTILE function

angerplea

Registered User.
Local time
Today, 01:32
Joined
Apr 18, 2012
Messages
22
Hi - I was wondering if there is a funtion in Access similar to NTILE or if I need to create a custome function for this. Anyway I'm out of idea's.

Basically I would like to calulate top percent of values contained in a cost column of ~15,000 records.

The top 75% of values = A, etc.

IIF([MyUnitCost]=75%,"A","B") if something like this mock up worked I might try a nested if to add "C".

NTILE does not appear to work in Access.
TOP PERCENT seemed to return top 75% of to records, not values.
RANK did not seem to work either.

I probably have overlooked a simple way to do this.

Thanks
 
To get the Top predicates to work properly, you need an order by clause. So you would order ascending or descending depending on whether you wanted the smallest or largest values.
 
Thanks Pat

I have tried a few different ways but get error msg's

SELECT *
,NTILE(4) OVER(ORDER BY [qry_4].[MyCost] DESC) AS Quartile
FROM qry_4


My syntax must be off.:(:(:(
 
NTILE() is not a Jet/ACE function nor is OVER(). You can ONLY use these if your BE is SQL Server and you create a pass-through query. That's why you were using TOP.
 

Users who are viewing this thread

Back
Top Bottom