Hi,

I have a table with two columns of data: LA and VALUE. The column is
sorted by VALUE, in ascending order.

I have a recordset that counts the number of records in the table and
stores this as a variable (TotalRecords). I have another variable
that calculates the record that is 1/4 of the way down (Quart1 =
TotalRecords / 4)

I now need to be able to select the VALUE from the table where the
record number = QUART1 and store this value as another variable (for
use in reports, queries and a table later on).

Is this possible or is there a better way to do this? I havent got a
sort id in the table - is this the only way to do it? If so, is there
a better way to add a a sort id to a table automatically using VBA
code?

Many thanks

Laura

On Mar 31, 1:34*pm, Laura <laurajayne.coz... (AT) peterborough (DOT) gov.uk>
wrote:
Laura,

Perhaps you can find some useful information here:

SQL for calculating percentiles:
http://groups.google.com/group/comp....404777424676dc

Query with a rownumber:
http://groups.google.com/group/comp....9960a085d2f378

James A. Fortune
CDMAPoster (AT) FortuneJames (DOT) com

Also, I believe "Value" is an Access reserved word, and using it as a
variable name can, though it won't necessarily and consistently, cause
"unexpected results". Access MVP Allen Browne has an "issue checker" that
checks for things that potentially can cause problems in your database.
Among many other things, it checks for improper use of reserved words.
You'll find it at:

http://allenbrowne.com/appissuechecker.html

Larry Linson
Microsoft Office Access MVP

"Laura" <laurajayne.cozens (AT) peterborough (DOT) gov.uk> wrote

Thansk for the links James, but the first one doesnt have an answer on
it? This is what I need to do!

Larry, VALUE is a field name and not a variable, so hopefully this
will be ok (unfortunately, the table is not mine and I cant amend it -
there is also 300 tables with the same field name, so its quite a lot
to have to change!). It hasnt caused any problems with the other
things thats happening with it (yet!).

Many thanks to you both for taking the time to reply,

Laura

On Apr 1, 5:21*am, Laura <laurajayne.coz... (AT) peterborough (DOT) gov.uk>
wrote:
You're quite right. The link for the entire thread is:

http://groups.google.com/group/comp....404777424676dc

Note: In the final post of that thread use:

25 AS PercentileGroup

to get quartiles. If that doesn't work, I suppose you could use the
rownumbers, but it should supply the information you need.

James A. Fortune
CDMAPoster (AT) FortuneJames (DOT) com