Welcome to SPN

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

Sign Up Now!

Divide a table of records into 3 fairly even groups

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

  1. Gina

    Gina
    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.

    --
    Gina
     
  2. Loading...


  3. Douglas J. Steele

    Douglas J. Steele
    Expand Collapse
    Guest

    Try creating a query that has a computed field Remainder: [AutonumberFIeld]
    Mod 3

    You can then have 3 queries based on that which takes those with Remainder =
    0, Remainder = 1 and Remainder = 2.

    --
    Doug Steele, Microsoft Access MVP
    http://I.Am/DougSteele
    (no private e-mails, please)


    "Gina" <Gina@discussions.microsoft.com> wrote in message
    news:0EACABF1-CF16-4CFA-B540-FA638714DEA7@microsoft.com...
    > 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.
    >
    > --
    > Gina
     
  4. Gina

    Gina
    Expand Collapse
    Guest

    Thanks for your reponse. I am not sure what you mean by 'mod'. Although, it
    sounds like just what I'm looking for. Please explain.

    Thanks
    --
    Gina


    "Douglas J. Steele" wrote:

    > Try creating a query that has a computed field Remainder: [AutonumberFIeld]
    > Mod 3
    >
    > You can then have 3 queries based on that which takes those with Remainder =
    > 0, Remainder = 1 and Remainder = 2.
    >
    > --
    > Doug Steele, Microsoft Access MVP
    > http://I.Am/DougSteele
    > (no private e-mails, please)
    >
    >
    > "Gina" <Gina@discussions.microsoft.com> wrote in message
    > news:0EACABF1-CF16-4CFA-B540-FA638714DEA7@microsoft.com...
    > > 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.
    > >
    > > --
    > > Gina

    >
    >
    >
     
  5. PC Datasheet

    PC Datasheet
    Expand Collapse
    Guest

    Mod returns the remaninder from a division.
    Ex.: 20 Mod 3 = 2
    ie, the division result is 6 with remainder 2

    also, 26 Mod 3 = 2
    also, 29 Mod 3 = 2


    --
    PC Datasheet
    Your Resource For Help With Access, Excel And Word Applications
    Over 1175 users have come to me from the newsgroups requesting help
    resource@pcdatasheet.com

    "Gina" <Gina@discussions.microsoft.com> wrote in message
    news:21E5B8D3-F5CF-44A9-94A2-DE428B723564@microsoft.com...
    > Thanks for your reponse. I am not sure what you mean by 'mod'. Although,
    > it
    > sounds like just what I'm looking for. Please explain.
    >
    > Thanks
    > --
    > Gina
    >
    >
    > "Douglas J. Steele" wrote:
    >
    >> Try creating a query that has a computed field Remainder:
    >> [AutonumberFIeld]
    >> Mod 3
    >>
    >> You can then have 3 queries based on that which takes those with
    >> Remainder =
    >> 0, Remainder = 1 and Remainder = 2.
    >>
    >> --
    >> Doug Steele, Microsoft Access MVP
    >> http://I.Am/DougSteele
    >> (no private e-mails, please)
    >>
    >>
    >> "Gina" <Gina@discussions.microsoft.com> wrote in message
    >> news:0EACABF1-CF16-4CFA-B540-FA638714DEA7@microsoft.com...
    >> > 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.
    >> >
    >> > --
    >> > Gina

    >>
    >>
    >>
     
  6. KARL DEWEY

    KARL DEWEY
    Expand Collapse
    Guest

    Add an output field in the design grid using the autonumber field like this --
    Remainder: [YourAutoNumberField] Mod 3

    For the first batch use criteria of 0 (zero). The second batch use 1.

    "Gina" wrote:

    > Thanks for your reponse. I am not sure what you mean by 'mod'. Although, it
    > sounds like just what I'm looking for. Please explain.
    >
    > Thanks
    > --
    > Gina
    >
    >
    > "Douglas J. Steele" wrote:
    >
    > > Try creating a query that has a computed field Remainder: [AutonumberFIeld]
    > > Mod 3
    > >
    > > You can then have 3 queries based on that which takes those with Remainder =
    > > 0, Remainder = 1 and Remainder = 2.
    > >
    > > --
    > > Doug Steele, Microsoft Access MVP
    > > http://I.Am/DougSteele
    > > (no private e-mails, please)
    > >
    > >
    > > "Gina" <Gina@discussions.microsoft.com> wrote in message
    > > news:0EACABF1-CF16-4CFA-B540-FA638714DEA7@microsoft.com...
    > > > 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.
    > > >
    > > > --
    > > > Gina

    > >
    > >
    > >
     
  7. Al Camp

    Al Camp
    Expand Collapse
    Guest

    Gina,
    You should to stay with your original 5/9 post on this question.
    Please see James Fortune's response to your reply of today (5/12)... on that original
    thread.
    Starting a duplicate thread will only lead to confusion, and duplicated effort...

    Al Camp
    Candia Computer Consulting - Candia NH
    http://home.comcast.net/~cccsolutions


    "Gina" <Gina@discussions.microsoft.com> wrote in message
    news:0EACABF1-CF16-4CFA-B540-FA638714DEA7@microsoft.com...
    > 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.
    >
    > --
    > Gina
     
  8. Gina

    Gina
    Expand Collapse
    Guest

    Al,

    Using this site is new to me. I thought that if you weren't on the first
    few pages, that you would soon be forgotten. I did go back and check on
    James response. As I am rec'g alternate solutions, I am going to see which
    is the best fit. I will respond to James and let him know that I have
    resubmitted my question here so that he can also see what others have
    suggested. The last thing I want is for anyone to being doing duplicate
    work. Happens to be one of my own pet peeves.

    Thanks, and sorry for any inconvenience.
    --
    Gina


    "Al Camp" wrote:

    > Gina,
    > You should to stay with your original 5/9 post on this question.
    > Please see James Fortune's response to your reply of today (5/12)... on that original
    > thread.
    > Starting a duplicate thread will only lead to confusion, and duplicated effort...
    >
    > Al Camp
    > Candia Computer Consulting - Candia NH
    > http://home.comcast.net/~cccsolutions
    >
    >
    > "Gina" <Gina@discussions.microsoft.com> wrote in message
    > news:0EACABF1-CF16-4CFA-B540-FA638714DEA7@microsoft.com...
    > > 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.
    > >
    > > --
    > > Gina

    >
    >
    >
     
  9. James A. Fortune

    James A. Fortune
    Expand Collapse
    Guest

    Gina wrote:
    > Al,
    >
    > Using this site is new to me. I thought that if you weren't on the first
    > few pages, that you would soon be forgotten. I did go back and check on
    > James response. As I am rec'g alternate solutions, I am going to see which
    > is the best fit. I will respond to James and let him know that I have
    > resubmitted my question here so that he can also see what others have
    > suggested. The last thing I want is for anyone to being doing duplicate
    > work. Happens to be one of my own pet peeves.
    >
    > Thanks, and sorry for any inconvenience.


    I saw the new post. One thing to consider is that the solution using
    the Mod function will break up the table with every third row going into
    a new table. You need to decide whether that's desirable or not.
    Douglas' solution and mine should both work without much effort. Plus,
    we're standing by, eager to assist. If you don't need or want to keep
    the records contiguous then use Douglas' solution since it is easier to
    implement.

    James A. Fortune
    MPAPoster@FortuneJames.com
     
  10. StopThisAdvertising

    StopThisAdvertising
    Expand Collapse
    Guest

    "PC Datasheet" <NoSpam@Spam.Com> schreef in bericht news:Ce79g.3661$u4.113@newsread1.news.pas.earthlink.net...
    --
    > PC Datasheet
    > Your Resource For Help With Access, Excel And Word Applications 'Resource ????
    > Over 1175 users have come to me from the newsgroups requesting help '1175 users ????
    > resource@pcdatasheet.com


    --
    To Steve:
    You have been away only for a little while
    Since no-one wants your advertising/job hunting here you are allowed to stay away longer!

    Over 825 !! users from the newsgroups have visited the website to read what kind of a 'resource' you are... (rapidly increasing..)

    To the original poster:
    Most people here have a common belief that the newsgroups are for *free exchange of information*.
    But Steve is a notorious job hunter in these groups, always trying to sell his services.
    ==> And he is also known here as a shameless liar with *no ethics at all.*

    Before you intend to do business with him look at:
    http://home.tiscali.nl/arracom/whoissteve.html

    Arno R
     
  11. Gina

    Gina
    Expand Collapse
    Guest

    James,

    I ended up using the MOD 3, works wonderfully for this project.

    I learn something new everyday.

    I want to thank you all for your time and input.
    --
    Gina


    "James A. Fortune" wrote:

    > Gina wrote:
    > > Al,
    > >
    > > Using this site is new to me. I thought that if you weren't on the first
    > > few pages, that you would soon be forgotten. I did go back and check on
    > > James response. As I am rec'g alternate solutions, I am going to see which
    > > is the best fit. I will respond to James and let him know that I have
    > > resubmitted my question here so that he can also see what others have
    > > suggested. The last thing I want is for anyone to being doing duplicate
    > > work. Happens to be one of my own pet peeves.
    > >
    > > Thanks, and sorry for any inconvenience.

    >
    > I saw the new post. One thing to consider is that the solution using
    > the Mod function will break up the table with every third row going into
    > a new table. You need to decide whether that's desirable or not.
    > Douglas' solution and mine should both work without much effort. Plus,
    > we're standing by, eager to assist. If you don't need or want to keep
    > the records contiguous then use Douglas' solution since it is easier to
    > implement.
    >
    > James A. Fortune
    > MPAPoster@FortuneJames.com
    >
     

Share This Page