Groups | Search | Server Info | Keyboard shortcuts | Login | Register [http] [https] [nntp] [nntps]

Groups > comp.databases.ms-access > #725 > unrolled thread

Selecting a value from a particluar record number/Quartiles

Back to article view | Back to comp.databases.ms-access

Contents

  Selecting a value from a particluar record number/Quartiles Laura <laurajayne.cozens@peterborough.gov.uk> - 2011-03-31 10:34 -0700
    Re: Selecting a value from a particluar record number/Quartiles "James A. Fortune" <CDMAPoster@FortuneJames.com> - 2011-03-31 12:28 -0700
    Re: Selecting a value from a particluar record number/Quartiles "Access Developer" <accdevel@gmail.com> - 2011-03-31 21:33 -0500
      Re: Selecting a value from a particluar record number/Quartiles Laura <laurajayne.cozens@peterborough.gov.uk> - 2011-04-01 02:21 -0700
        Re: Selecting a value from a particluar record number/Quartiles "James A. Fortune" <CDMAPoster@FortuneJames.com> - 2011-04-01 12:23 -0700

#725 — Selecting a value from a particluar record number/Quartiles

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

[toc] | [next] | [standalone]

#726

On Mar 31, 1:34 pm, Laura <laurajayne.coz...@peterborough.gov.uk>
wrote:
> 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

Laura,

Perhaps you can find some useful information here:

SQL for calculating percentiles:
http://groups.google.com/group/comp.databases.ms-access/msg/54404777424676dc

Query with a rownumber:
http://groups.google.com/group/comp.databases.ms-access/msg/a09960a085d2f378

James A. Fortune
CDMAPoster@FortuneJames.com

[toc] | [prev] | [next] | [standalone]

#738

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@peterborough.gov.uk> wrote in message 
news:fbb8fa76-22e4-4a77-94f2-df0e5f8e726e@a17g2000yqn.googlegroups.com...
> 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 

[toc] | [prev] | [next] | [standalone]

#740

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

[toc] | [prev] | [next] | [standalone]

#753

On Apr 1, 5:21 am, Laura <laurajayne.coz...@peterborough.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!

You're quite right.  The link for the entire thread is:

http://groups.google.com/group/comp.databases.ms-access/browse_frm/thread/1789b9f773475c5a/54404777424676dc#54404777424676dc

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@FortuneJames.com

[toc] | [prev] | [standalone]

Back to top | Article view | comp.databases.ms-access

csiph-web