Divide a table of records into 3 fairly even groups

  • Thread starter Thread starter Guest
  • Start date Start date
G

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.
 
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.
 
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 said:
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

(no private e-mails, please)


Gina said:
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.
Click to expand...
Click to expand...
 
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
(e-mail address removed)

Gina said:
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 said:
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

(no private e-mails, please)


Gina said:
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.
Click to expand...
Click to expand...
Click to expand...
 
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
 
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 said:
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 said:
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

(no private e-mails, please)


Gina said:
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.
Click to expand...
Click to expand...
Click to expand...
 
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 said:
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.
Click to expand...

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
(e-mail address removed)
 
PC Datasheet said:
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 ????
(e-mail address removed)
Click to expand...

--
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
 
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.
 
Back
Top