Dividing a table into 3 even groups

[Gina]

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.

 

[James A. Fortune]

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
(e-mail address removed)

 

[Gina]

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.

 

[James A. Fortune]

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
(e-mail address removed)

 

[Gina]

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.

 

[James A. Fortune]

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
(e-mail address removed)