Welcome to SPN

Register and Join the most happening forum of Sikh community & intellectuals from around the world.

Sign Up Now!

Dividing a table into 3 even groups

Discussion in 'Information Technology' started by Gina, Jul 28, 2006.

  1. Gina

    Gina
    Expand Collapse
    Guest

    I need to divide a table of approximately 1200 records into three fairly even
    groups. So far, I have created an autonum field and get the total and on a
    calculator divide by 3 and then create 3 identical queries. In one I put
    '<400' in another 'Between 400 And 800' and in the last one '>800'.

    However, each day this table's total changes and thus these subsequent
    totals change.

    Is there a way to automate this.

    Thank you.
    --
    Gina
     
  2. Loading...

    Similar Threads Forum Date
    Caste cutting: Clash of identities dividing Sikhs Hard Talk May 28, 2009
    As A Child, Public Marches Of Sikhism Made Me Uncomfortable. They Still Do . Why ? Whats The Logic Hard Talk Oct 16, 2016
    Arts/Society Backyard Vegetable Garden Language, Arts & Culture Dec 27, 2013
    Heritage Now a Vegetable Market, Ranjit Singh's Royal Haveli a Picture of Neglect History of Sikhism Nov 11, 2013
    Sikhism Helium: 1984 and the "Periodic Table of Hate" (Jaspreet Singh) Book Reviews & Editorials Oct 28, 2013

  3. James A. Fortune

    James A. Fortune
    Expand Collapse
    Guest

    Gina wrote:
    > I need to divide a table of approximately 1200 records into three fairly even
    > groups. So far, I have created an autonum field and get the total and on a
    > calculator divide by 3 and then create 3 identical queries. In one I put
    > '<400' in another 'Between 400 And 800' and in the last one '>800'.
    >
    > However, each day this table's total changes and thus these subsequent
    > totals change.
    >
    > Is there a way to automate this.
    >
    > Thank you.


    Hi Gina,

    One way to get the first third of a set of records with contiguous ID's
    is to retrieve the number of records using a subquery:

    First third:

    SELECT ColorID, ColorName FROM tblColors WHERE ColorID <= (SELECT
    Count(*) FROM tblColors AS A) \ 3;

    Second third:

    SELECT ColorID, ColorName FROM tblColors WHERE ColorID > (SELECT
    Count(*) FROM tblColors AS A) \ 3 AND ColorID <= 2 * (SELECT Count(*)
    FROM tblColors AS A) \ 3;

    Third third:

    SELECT ColorID, ColorName FROM tblColors WHERE ColorID > 2 * (SELECT
    Count(*) FROM tblColors AS A) \ 3;

    James A. Fortune
    MPAPoster@FortuneJames.com
     
  4. Gina

    Gina
    Expand Collapse
    Guest

    James,
    Thanks for the reply. I have been trying your script, but I must not be
    understanding everything. I am not sure what 'AS A' in - tblcolors AS A) \ 3
    - is. Do I need to DIM something as 'A'? Is 'A' where the total count is
    held. I'm sure my ignorance is showing. Please help.
    --
    Gina


    "James A. Fortune" wrote:

    > Gina wrote:
    > > I need to divide a table of approximately 1200 records into three fairly even
    > > groups. So far, I have created an autonum field and get the total and on a
    > > calculator divide by 3 and then create 3 identical queries. In one I put
    > > '<400' in another 'Between 400 And 800' and in the last one '>800'.
    > >
    > > However, each day this table's total changes and thus these subsequent
    > > totals change.
    > >
    > > Is there a way to automate this.
    > >
    > > Thank you.

    >
    > Hi Gina,
    >
    > One way to get the first third of a set of records with contiguous ID's
    > is to retrieve the number of records using a subquery:
    >
    > First third:
    >
    > SELECT ColorID, ColorName FROM tblColors WHERE ColorID <= (SELECT
    > Count(*) FROM tblColors AS A) \ 3;
    >
    > Second third:
    >
    > SELECT ColorID, ColorName FROM tblColors WHERE ColorID > (SELECT
    > Count(*) FROM tblColors AS A) \ 3 AND ColorID <= 2 * (SELECT Count(*)
    > FROM tblColors AS A) \ 3;
    >
    > Third third:
    >
    > SELECT ColorID, ColorName FROM tblColors WHERE ColorID > 2 * (SELECT
    > Count(*) FROM tblColors AS A) \ 3;
    >
    > James A. Fortune
    > MPAPoster@FortuneJames.com
    >
     
  5. James A. Fortune

    James A. Fortune
    Expand Collapse
    Guest

    Gina wrote:
    > James,
    > Thanks for the reply. I have been trying your script, but I must not be
    > understanding everything. I am not sure what 'AS A' in - tblcolors AS A) \ 3
    > - is. Do I need to DIM something as 'A'? Is 'A' where the total count is
    > held. I'm sure my ignorance is showing. Please help.


    The 'AS A' was simply to point out that a subquery is being used to get
    the count. If no fields are referenced, the 'AS A' is not required. I
    think you just need to change the table name to your table name and
    change the ID field and the other field to your field names. If you
    have trouble, post back with your table name, its field names and a
    sample row. Someone should be able to provide an appropriate SQL string.

    James A. Fortune
    MPAPoster@FortuneJames.com
     
  6. Gina

    Gina
    Expand Collapse
    Guest

    James,

    First let me start with an apology, because I value your time. I
    re-submitted my question so that it would show up on the first page again. I
    thought that if you were not on the first few, you would soon be forgotten.
    But your response has shown me that this thinking is not correct. I rec'd
    some alternate solutions that you might be interested in looking at. You
    will find them currently on the first page.

    I will stick to my orginial submissions from now on.
    --
    Gina


    "James A. Fortune" wrote:

    > Gina wrote:
    > > James,
    > > Thanks for the reply. I have been trying your script, but I must not be
    > > understanding everything. I am not sure what 'AS A' in - tblcolors AS A) \ 3
    > > - is. Do I need to DIM something as 'A'? Is 'A' where the total count is
    > > held. I'm sure my ignorance is showing. Please help.

    >
    > The 'AS A' was simply to point out that a subquery is being used to get
    > the count. If no fields are referenced, the 'AS A' is not required. I
    > think you just need to change the table name to your table name and
    > change the ID field and the other field to your field names. If you
    > have trouble, post back with your table name, its field names and a
    > sample row. Someone should be able to provide an appropriate SQL string.
    >
    > James A. Fortune
    > MPAPoster@FortuneJames.com
    >
     
  7. James A. Fortune

    James A. Fortune
    Expand Collapse
    Guest

    Gina wrote:
    > James,
    >
    > First let me start with an apology, because I value your time. I
    > re-submitted my question so that it would show up on the first page again. I
    > thought that if you were not on the first few, you would soon be forgotten.
    > But your response has shown me that this thinking is not correct. I rec'd
    > some alternate solutions that you might be interested in looking at. You
    > will find them currently on the first page.
    >
    > I will stick to my orginial submissions from now on.


    Gina,

    Please see my comments in the first thread. I took no offense.
    Normally, what Douglas submitted would have been my first thought, but
    the ranges you used suggested that you might need to keep the records
    together. I should have asked you if that was a requirement. Anyway,
    it looks like you'll soon have a solution that does what you need with
    both Douglas and I working on it. I'm just interested in you getting a
    good solution for your situation and understanding the tradeoffs between
    the different solutions presented. Please don't be afraid to ask any
    questions you have.

    I hope this helps,

    James A. Fortune
    MPAPoster@FortuneJames.com
     

Share This Page