groups with more than 20 line items in a report.


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.
 
Ad

Advertisements

M

Marshall Barton

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.


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.
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top