NTILE function

[angerplea]

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

 

[Pat Hartman]

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.

 

[angerplea]

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.

 

[Pat Hartman]

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.