Advanced sort in an Access Report.

[TejaGrand]

I am a user of Access, but not very good at itand I have encountered something that is giving me a migraine.


Here's the sitch:
I have 5 tables.

Each Area has ReportNos which have a number ofFindingNos and each finding has a number RecommendationNos. EachRecommendationNo has a Calculated Target Completion Date. The RecommendationNois not always completed on time, so they are given a Calculated Followup datefor each time they have to follow Up.

The users of this database want a calculatedfield we have named "Disposition" which will compare the[tblRecommendation].[Calculated Target Completion Date] to the[tblFollowupPeriod].[Calculated Followup] and return with "OnSchedule" or "Past Due." I have created that in the query andits working fine. Here's how the datasheet looks:

HERE IS MY ISSUE. In their report, they want theArea at the top, and then they want the report numbers with any "PastDue" dispositions to be listed at the top of the report, and those with noPast Due dispositions at the end of the report.
For example:

Here's my current report. How would I sort it toget the above results?? Its driving me nuts. THANK YOU!!

[June7]

If you want to provide db for analysis and testing, follow instructions at bottom of my post.

I think need to calculate a field that indicates whether there are any past due recommendations for each report number. One way is with DCount() or DLookup() domain aggregate function.