Sorting 'n grouping records in a report = easy. How about trying to sort 'n group those same recs fed to a Function???

[MLH]

I have a challenge...

In a table I call tblStuff4Letter s with 3 fields:
[Cust_ID], [RecipientName] and [BodyText]. I have
a report called rptOutboundCorr espondence that feeds
off this table. Sorting & grouping is turned on in
that report - sorted first on [Cust_ID] (ascending)
then on [RecipientName] (ascending). Of course,
there are more supporting fields in the table. The
above 3 are the significant fields for this discussion.

The report basically outputs letters from customers
to recipients. If tblStuff4Letter s has 4 records with
[Cust_ID]=1 and 2 of the records have [RecipientName]
values of "Microsoft" and the other 2 have "Compaq"
as their [RecipientName] values, the output will be
1 letter to Microsoft with 2 paragraphs [[BodyText])
and 1 letter to Compaq with 2 paragraphs.

On a routine basis, the data in tblStuff4Letter s gets
blown away and repopulated with thousands of records
that result in hundreds of letters from customers to
companies with anywhere from 1 to a dozen paragraphs
each. There is 1 record in tblStuff4Letter s for each
paragraph that needs printing. Thus, 1000 records in
tblStuff4Letter s could result in 100 letters containing
10 paragraphs each being printed. It could be 200 letters
with 5 paragraphs each or 1 letter with 1000 paragraphs.

I do these print jobs in a batch. My current method
is to print all the paper, separate the individual
letters and mail them. I'm tired of mailing them and
I've told the customers that I'm now going to convert
my procedures from snail mailing hard copies to emailing
files. Trouble is, I don't know how to create the 100
files (in the above example). I only know how to print
the 1000 paragraphs. I know how to create 1 big file of
all the letters containing all 1000 paragraphs. But I
don't know how to make Access create a file for just
the [BodyText] paragraphs for the first [Cust_ID] /
[RecipientName], then have it make a file for the next
[Cust_ID] / [RecipientName] combo & the next & the next
and so on... I could just send EVERYBODY the 1 great
big file & have 'em cut 'n paste the relatively few
letters pertaining to them personally - tossing the
rest (which would be the bulk of the file). But that's
not gonna make the customers happy. Does anyone have
any suggestions?

Lets just say, for argument's sake, that I've got a
function MTF - which stands for MakeTheFile that I can
feed chunks of tblStuff4Letter s to (one letter at a time)
and that Function MTF() will take care of making the file,
how do I go down through the table grabbing just those
records needed for each letter - a "chunk" of records
being those with a unique [Cust_ID] / [RecipientName]
combo.

[Robbo]

Just an idea, but it should work.

Open recordset with SQL = "SELECT [Cust_ID], [RecipientName] FROM
tblStuff4Letter s GROUP BY [Cust_ID], [RecipientName]"

Loop through recordset.

For each record open report in design view, set Filter to [Cust_ID] =
rst![Cust_ID] AND [RecipientName] = rst![RecipientName], set FilterOn
To True. Close and Save form then export to .rtf file with an
appropriate name.

This should produce a seperate .rtf file for each [Cust_ID],
[RecipientName] combination
--
Posted via http://dbforums.com