Do you just want to see all the records that only start with A and a have the 'used' field summed, or do you want to see it as you have it and have the 'used' field for each group summed?
If it's the first way then just a find for records that start with the A in Role# should do the trick, sorted by roll number with the sum field in the footer should work
Thank for taking the time to write Steve! That is true. And while it works, in this particular case the found set of records is filtered by day. The records shown above pertain to the first day and are sorted by Roll # but are mixed (they contain A, B, and S types) and must be shown together in the report. I wish to show at the top of the report the subtotals of each record subgroup seperately (A = Sum of A, B = Sum of B, S = Sum of S) . Like a summary but not at the top or bottom of each sorted group,instead at the top of the report.
Assuming you have a primary key "RecordID" in your table:
- add a summary field 'List of' "IDList" to your table
then you can fill 3 $$variables like this:
$$SumA = Let ( [
_ids = Substitute ( YourTable::IDList ; "¶" ; "," )
ExecuteSQL ( "
WHERE \"Roll #\" LIKE ? AND
RecordID IN(" & _ids & ")
" ; "" ; "" ; "'A%'" )
) //end let
do the same for $$SumB and $$SumS