x percentile query

[Kayak]

Hi all

i want to make query of data from my x% of the records.i
have done it in two ways but both seems not working with
me.

/////first way
i was using sub query to get 80% of total record then get
the last record of that query using code below. but when i
executed it,a window error "at most one record can be
return by subquery"

SELECT Last(NewTable.IDold) AS IDold
FROM NewTable
HAVING (((Last(NewTable.IDold))=(SELECT TOP 80 PERCENT
NewTable.IDold FROM NewTable));

/////second way
im using xpercentile query based on
http://www.mvps.org/access/queries/qry0019.htm but i get
error too.i dont know where the missing/error part are

xpercentile : DMin("[IDold]","[Newtable]","DCount
(""*"", """ & [NewTable] & """, """ & [Idold] & "<="" & ["
& [IDold] & " ]) >= " & 0.8*DCount("*",[NewTable]))

please help

 

[Michel Walsh]

Hi,

query19.htm does not handle the presence of NULL.


The sub query return many records, so many IDold, say, 1 2 3 and 4 (four
rows, one column). So, how can a value, x, be equal to and 1, and 2, and 3
and 4 at the same time? can't. So, that is why you get the error in the
first case. You also missed to specify an order by clause, it is generally
important when using the TOP construction.

you can try:

SELECT TOP 1 x.*
FROM (SELECT TOP 80 PERCENT *
FROM myTable
ORDER BY f1 DESC) As x
ORDER BY x.f1 ASC



Hoping it may help,
Vanderghast, Access MVP