Need report to separate records

[PJHAction]

I query a class by class number (08-027), but now I want to break that class down by A, B, or C Group based on number of students in query, this week it could be 80, next week 110.

What I am looking at.
- Run a query to pull everyone in that class (08-027)
- Based on the class size (lets say 75) divide it by 3
- Print a report for the first 25 as group A, next 25 as group B and last 25 as group C.

I dont know where to start on this!

Peter

 

[Remou]

Here is a query, it will return 0-n as the group, be aware that a report based on this query will take some time (<>minutes) to run if you have more than a few hundred records

SELECT t.ID, t.Class, DCount("ID","t","ID<=" & [ID])\25 AS C
FROM t;

 

[PJHAction]

OK,I can see how to get the total records from query, but now to get a report for 3 groups is the problem.

Peter

 

[Remou]

You now have a query with the first 25 records numbered zero, the next 25 numbered 1 and so on. You can then group on "C", the count field, in your report.

 

[PJHAction]

Actually sounds easy, I will try on Monday at work. Thanks so much.

Peter

 

[ESquared]

Instead of \ 25, do Mod 3, then you will always get three groups without having to know the size of each group (such as 25 for a group of 75). If you want the first record to start at 0 then change the DCount to < instead of <=.

If performance is still bad then some helper queries (such as one that returns {{0, ID of 25th row}, {1, ID of 50th row}, {2, ID of 75 row}} ) or custom functions (to provide similar information) could really improve performance.

 

[PJHAction]

This query does not assign 1, 2, 3

SELECT abc.LastName, abc.SSN, DCount("ssn","tblsstudents","ssn<=" & [ssn]) Mod 3 AS C
FROM abc
GROUP BY abc.LastName, abc.SSN;

Results

Frank 555-55-5551 1
Sam 555-55-5552 2
Homeboy 555-55-5553 1

Would like the Mod num to be 1, 2, 3 and then repeat

Any Ideas?