groups with more than 20 line items in a report.

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

Guest

I want to create a report that will put a break every 20 lines and subtotal
those records, and then grand total at the end. This should work within
groups of records. i.e. I have 4 different groups of info, group 1 has 10
records, group 2 has 25 records, group 3 has 17 and group 4 has 60. I have
the report set to break on each group and give a subtotal for that group, but
I need to further break down groups 2 & 4 into 20 items then a subtotal and
so forth until all the records have been totalled for that group and then
give an over all total for that group.
 
Cyberwolf said:
I want to create a report that will put a break every 20 lines and subtotal
those records, and then grand total at the end. This should work within
groups of records. i.e. I have 4 different groups of info, group 1 has 10
records, group 2 has 25 records, group 3 has 17 and group 4 has 60. I have
the report set to break on each group and give a subtotal for that group, but
I need to further break down groups 2 & 4 into 20 items then a subtotal and
so forth until all the records have been totalled for that group and then
give an over all total for that group.
Click to expand...


It think the easiest way to do this is to is add a
calculated field to the report's record source query.

(SELECT Count(*)
FROM table as X
WHERE X.groupfield = table.groupfield
AND X/sortfield < table.sortfield) \ 20 As LineGrp

Then add a group to the report using the calculated field.

Note that the sort field must be on a unique field to avoid
confusion over multiple records being counted as the same.
 
Back
Top