>I recently created a report for an old application. In the app, the comments are stored as a series of six records with the table structure being a key field and then a 60 character field for the text. So for each comment there will be six records in the table. I wanted to select these into a memo field along with other fields from related tables for inclusion in a report.
>
>I selected the matching records with a sql select by the key field and then used a scan loop to gather the six records into a cursor to combine the six lines into one memo field. This works fine but I am wondering why I couldn't do this with a select statment and not have to rely on the scan loop. For some reason, I am drawing a blank.
>
>Any ideas?
>
>Thanks,
>Bill

Bill,
With SQL Server, most likely the structure wouldn't have been designed this way. :-)

IAC, even though you have a solution, one suggestion would be to change the report. If the report is in VFP, add a Report Grouping on the key field. Put the non-Comment fields in the Group Header. The Comments can be displayed in one of two ways.

1. Add the Comments field to the Detail band and each one will be displayed one after the other on a separate line.
2. Create a report variable that stores the concatendated contents of teh Comments field. Reset it at the end of the Group and display the value of the report variable in the Group footer. This functionality is the same as writing a UDF for the SQL statement but unless the end user needs to see the raw data, it really doesn't need to be done there disturbing the optimization process.

The SQL select for this would simply gather all the records and sort on the key field.

HTH.