choping up data

[ryan.clair]

I have X amount of records and I want to chop them up into groups of
25.

Then I'd like to add another column to each group (or table) and assign
a unique value to each group.

Thanks for your help in advance.

 

[Duane Hookom]

You need to identify what value stored in a field or fields decides which of
the 25 groups each record would go into.

 

[ryan.clair]

Actually I'm taking an unknown number of records and dividing them up
in groups of 25... not 25 groups.

Lets say I use their ID as the identifier....

 

[BruceM]

Why do wish to "chop them up"?

 

[Duane Hookom]

There have been several postings in these groups about creating a "ranking"
value that numbers records 1- whatever. If you integer divide the ranking
value by 25, you will get groups of 25.

Group25: [RankValue]\25

 

[Vincent Johns]

There have been several postings in these groups about creating a "ranking"
value that numbers records 1- whatever. If you integer divide the ranking
value by 25, you will get groups of 25.

Group25: [RankValue]\25

For example (but I used 5 instead of 25 to save space in this message),
suppose you use field [MyTable_ID] as the unique identifier in the
following Table.

[MyTable] Table Datasheet View:

MyTable_ID Name
----------- ----------
-1281812111 Hillary
-1175407083 John
-819535950 Samuel
-32683379 Laura
100990686 Barbara
999344330 Bill
1229930059 James
1498042084 George
1834762503 George
1889883440 Mary
2053355580 John
2053355580 John

Then you can calculate group numbers using the following Query (but
change the 5 in the third line to 25)...

[Q_SerialNumbers] SQL:

SELECT MyTable.MyTable_ID, MyTable.Name,
Count(MT2.MyTable_ID) AS Num,
1+([Num]-1)\5 AS [Group]
FROM MyTable, MyTable AS MT2
WHERE (((MT2.MyTable_ID)<=[MyTable].[MyTable_ID]))
GROUP BY MyTable.MyTable_ID, MyTable.Name
ORDER BY Count(MT2.MyTable_ID);

[Q_SerialNumbers] Query Datasheet View:

MyTable_ID Name Num Group
----------- -------- ------ -----
-1281812111 Hillary 1 1
-1175407083 John 2 1
-819535950 Samuel 3 1
-32683379 Laura 4 1
100990686 Barbara 5 1
999344330 Bill 6 2
1229930059 James 7 2
1498042084 George 8 2
1834762503 George 9 2
1889883440 Mary 10 2
2053355580 John 11 3

.... and using these calculated group numbers you can display the results
using another Query.

[Q_Groups] SQL:

SELECT Q_SerialNumbers.Group, Q_SerialNumbers.Name,
Q_SerialNumbers.MyTable_ID
FROM Q_SerialNumbers
ORDER BY Q_SerialNumbers.Group, Q_SerialNumbers.Name;

[Q_Groups] Query Datasheet View:

Group Name MyTable_ID
----- -------- -----------
1 Barbara 100990686
1 Hillary -1281812111
1 John -1175407083
1 Laura -32683379
1 Samuel -819535950
2 Bill 999344330
2 George 1834762503
2 George 1498042084
2 James 1229930059
2 Mary 1889883440
3 John 2053355580

However, like BruceM, I think that this seems like a strange thing to do
to a list. You might want to consider sorting them by date, or
alphabetically, before assigning records to groups. Your identifier
(including the date or whatever) would still need to be unique.

 

[Duane Hookom]

Great example....!

--
Duane Hookom
MS Access MVP
--

There have been several postings in these groups about creating a
"ranking" value that numbers records 1- whatever. If you integer divide
the ranking value by 25, you will get groups of 25.

Group25: [RankValue]\25

For example (but I used 5 instead of 25 to save space in this message),
suppose you use field [MyTable_ID] as the unique identifier in the
following Table.

[MyTable] Table Datasheet View:

MyTable_ID Name
----------- ----------
-1281812111 Hillary
-1175407083 John
-819535950 Samuel
-32683379 Laura
100990686 Barbara
999344330 Bill
1229930059 James
1498042084 George
1834762503 George
1889883440 Mary
2053355580 John
2053355580 John

Then you can calculate group numbers using the following Query (but change
the 5 in the third line to 25)...

[Q_SerialNumbers] SQL:

SELECT MyTable.MyTable_ID, MyTable.Name,
Count(MT2.MyTable_ID) AS Num,
1+([Num]-1)\5 AS [Group]
FROM MyTable, MyTable AS MT2
WHERE (((MT2.MyTable_ID)<=[MyTable].[MyTable_ID]))
GROUP BY MyTable.MyTable_ID, MyTable.Name
ORDER BY Count(MT2.MyTable_ID);

[Q_SerialNumbers] Query Datasheet View:

MyTable_ID Name Num Group
----------- -------- ------ -----
-1281812111 Hillary 1 1
-1175407083 John 2 1
-819535950 Samuel 3 1
-32683379 Laura 4 1
100990686 Barbara 5 1
999344330 Bill 6 2
1229930059 James 7 2
1498042084 George 8 2
1834762503 George 9 2
1889883440 Mary 10 2
2053355580 John 11 3

... and using these calculated group numbers you can display the results
using another Query.

[Q_Groups] SQL:

SELECT Q_SerialNumbers.Group, Q_SerialNumbers.Name,
Q_SerialNumbers.MyTable_ID
FROM Q_SerialNumbers
ORDER BY Q_SerialNumbers.Group, Q_SerialNumbers.Name;

[Q_Groups] Query Datasheet View:

Group Name MyTable_ID
----- -------- -----------
1 Barbara 100990686
1 Hillary -1281812111
1 John -1175407083
1 Laura -32683379
1 Samuel -819535950
2 Bill 999344330
2 George 1834762503
2 George 1498042084
2 James 1229930059
2 Mary 1889883440
3 John 2053355580

However, like BruceM, I think that this seems like a strange thing to do
to a list. You might want to consider sorting them by date, or
alphabetically, before assigning records to groups. Your identifier
(including the date or whatever) would still need to be unique.

...

 

[ryan.clair]

Thank you for all your help. I will take a look at it later this
weekend- don't have the time now. But it looks like it will work.
Thanks again!

 

[ryan.clair]

I guess it help to say what the db is doing =)

There are X amount of students who must be placed into classrooms of Y
(in this example 25- if I can make this number dynamic all the better)
for test taking purposes.

The user imports their .xls that has all the students taking their test
(ID, First Name, Last Name, Grade). On top of having all these
students, there are some that have exceptions from taking the test-
some 10 different ones- and each exception has its own room.

After placing all the students into their rooms and the exceptions into
their rooms- I need to then sort them alphabetically (it is imperative
that I do this now and not before then). After which I need to print
off all of it in reports with the room number at the top and the 25 (or
however many the user specifies) students listed below.

The best way that I thought would be to place every student (including
exceptions) into one table with their appropriate rooms in a new column
so I can run the reports off it. Getting to that point has been the
issue.

I've been able to find the exceptions and place them within the same
table as the student's, find out the room numbers and place them into
a table and the room count (whole number), and of course extract all
the different exceptions from the normal students into their
appropriate tables.

So after I run this query (your example I think will work with a few
modifications) I need to do the same on the exceptions but it needs to
be a continuation of the room numbers.

Any thoughts before I start working on this tomorrow?

 

[Vincent Johns]

It's pretty easy to make this parametric, IF all the rooms are the same
size. To do that, just change the SQL of the Query I listed in an
earlier message to replace the number with a name like "RoomSize".

[Q_SerialNumbers] SQL, revised to include a parameter:

SELECT MyTable.MyTable_ID, MyTable.Name,
Count(MT2.MyTable_ID) AS Num,
1+([Num]-1)\RoomSize AS [Group]
FROM MyTable, MyTable AS MT2
WHERE (((MT2.MyTable_ID)<=[MyTable].[MyTable_ID]))
GROUP BY MyTable.MyTable_ID, MyTable.Name
ORDER BY Count(MT2.MyTable_ID);

When you try to run this Query, a dialogue box will pop up that asks you
to supply a number to use for RoomSize. You can enter 25 or whatever
suits your needs, and the Query will then spit out the records and group
numbers.

Actually, you can convert the group numbers to room assignments pretty
easily by setting up a Table in which each record contains a group
number (one field) and a room number (another field).

If the room sizes differ (one can accommodate 25 students, the next one
only 23, &c.), you can still do it, but it would be a little trickier.
In the Table listing the rooms, you'd need a field containing the
capacity of the room. You could then define a Query that identifies the
lowest (or highest) serial number of a student to be placed in that
room, and develop your list from that.

If you would like additional help, you might get better answers if you
post a (sanitized, and brief) list of records from the relevant Tables,
as well as the SQL of the Queries you're using.

Good luck with your project.

-- Vincent Johns <[email protected]>
Please feel free to quote anything I say here.

 

[ryan.clair]

Hey I can't thank you enough for all your help. I'd send you a fruit
basket- haha. Have a good weekend!

 

[Vincent Johns]

Thanks for the virtual fruit! Good luck with your database.

-- Vincent Johns <[email protected]>
Please feel free to quote anything I say here.