Find Max Length Remarks for Each Person in Group and Print That Record

[Rooski]

Hi,

I am using CR10 against an MS 2010 Access Database. This is a project database and for each project number in the database, the project officer will enter remarks that are stored in a Remarks table. The relationship is a one to many between the project number and the associated remarks. As well, each project officer has many different project numbers.

What I am trying to do is find, for each project officer, the one remark regardless of project number, that is greatest in length and then print only that remark and project number for that project officer. The report is grouped by project officer.

Since the remarks field is a string, I created a formula, @RemarksLen, to determine the length of each remark in the database:

Length ({Remarks})

I then created the following formula to capture the largest remarks length per officer and placed this formula in the Group Header:

Maximum ({@RemarksLen},{Project.Officer})

This does display the correct figure. What I can't figure out how to do is get the specific remark associated with this max length to display. When I place the remarks field from the table in the group header, it is not the correct remark. I was wondering if a group selection formula involving the Maximum command might work, but the command parameters don't seem to fit.

Thanks, Rooski

 

[kray4660]

One idea is to sort by Length ({Remarks}) in the Project officer Group and display {Remarks} Group Footer.

I hope this helps.

 

[lbass]

Are you saying you want the result to be placed in one instance of the Group Header for Project Officer (the one with the longest remarks)? Or are you trying to place it in the group header of a higher order group? Does it HAVE to be in a group header section?

-LB

 

[Rooski]

There is only one group in the report and, no, the results don't have to be in a group header section. All I want is for the longest remark (and its length) for each officer in the database to appear on the report. I only said they should appear in the group header as it seemed the most straight forward way to accomplish the task and that's where I managed to display the proper greatest length using the formula Maximum ({@RemarksLen},{Project.Officer}). All I was missing was the text of the actual remark. Perhaps the following layout will help in regard to how I want the report to appear sorted in ascending order by greatest remark length:

Project Officer Jones
Greatest Remark Length = 1,000 characters
Acutal text of greatest remark length

Project Officer Smith
Greatest Remark Length = 1,500 characters
Acutal text of greatest remark length

Project Officer Brown
Greatest Remark Length = 1,700 characters
Acutal text of greatest remark length

Etcetera

Again, I have managed to display the Greatest Remark Length for each officer, but not the actual text.

Does that help?

Thanks, Rooski

 

[Rooski]

I just tried kray4660's suggestion of sorting on Len(Remarks). I sorted in descending order and placed the remarks field in the group header. That displayed the output I was seeking in my last response. Good suggestion.

So please do not look further into this issue as it has been resolved.

Much thanks to both of you.

Regards, Rooski