472,119 Members | 1,584 Online
Bytes | Software Development & Data Engineering Community
Post +

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 472,119 software developers and data experts.

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

I have a challenge...

In a table I call tblStuff4Letters with 3 fields:
[Cust_ID], [RecipientName] and [BodyText]. I have
a report called rptOutboundCorrespondence 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 tblStuff4Letters 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 tblStuff4Letters 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 tblStuff4Letters for each
paragraph that needs printing. Thus, 1000 records in
tblStuff4Letters 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 tblStuff4Letters 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]
Nov 12 '05 #1
1 2506

Just an idea, but it should work.

Open recordset with SQL = "SELECT [Cust_ID], [RecipientName] FROM
tblStuff4Letters 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
Nov 12 '05 #2

This discussion thread is closed

Replies have been disabled for this discussion.

Similar topics

1 post views Thread by Jon via AccessMonster.com | last post: by
2 posts views Thread by Tim Marshall | last post: by
8 posts views Thread by sara | last post: by

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.