Sorting and Grouping Complexities

[bassman71]

Hello again my digi-Bretheren,

I have an Access report that needs to sum a sub-group's records within a main sort. So, let's say the main field grouped is called [Parent]. Now when the subgroup called [Checklist Type] changes records, I start a new group- No problem. I am even able to sum the sub-group within the main group with the following in the sub-group footer:=IIf([Checklist Type]="Not Found",Sum([Total]),"").
MY problem is twofold:
1. The sum expression in the [Parent] footer takes into account the entire [Checklist Type] field, while I'd like to subtract what was found in the =IIF[Checklist Type]... expression from the Summed number in the [Parent] footer
2. Can I alias those records in [Parent] field associated with the sub-group? So if the records in Parent read "None" while the [Checklist Type] reads Not Found, I want the [Parent] field's records to read "Not Found"

I know that's a mouthful, and it is easy to overcome by simply changing records in the underlying table, but I wanted to solve it within the report.

As always, many thanks.............Rich O

[OBP]

Rich, you might find this post useful, (or maybe not), where I do some similar simple VB calculations in the Detail and Page Footer sections.

http://forums.techguy.org/business-applications/527932-page-break-microsoft-access.html

Post #6 has the database with the report in question.

[XLGibbs]

Can't you just remove the filter for "Not Found" and have a subgrouping for that Checklist type autmatically? You can alias the parent field using an IIF statement IIF([Parent] = "none", "Not Found", [Parent])

Or you could assign a value to the subgroup instead of blank..
=IIf([Checklist Type]="Not Found",Sum([Total]),Sum(1))

Which would effectively "count" the "Not Found" Checklist type by artificially Summing 1 for each row that criteria is met.

Hope it is reasonably close..been awhile on Access reports..