473,382 Members | 1,354 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,382 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 2576

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 thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

by: Mat N | last post by:
Hi, I've been trying to work out how to create a report based on crosstab query for which the number of fields is variable. For example in a situation where you show customer billing by year in...
by: Mike MacSween | last post by:
tblCourses one to many to tblEvents. A course may have an intro workshop (a type of event), a mid course workshop, a final exam. Or any combination. Or something different in the future. At...
by: Megan | last post by:
quick summary: i'm having problems trying to group fields in a report in order to calculate percentages. to calculate percentages, i'm comparing the results from my grouped fields to the totals....
by: Jon via AccessMonster.com | last post by:
Hi Guys, My "Sorting and Grouping" in my report looks like this: Field/Expression Sort Order sID Ascending ((= Project Name Ascending User *** ...
by: Marie | last post by:
My report has a text field named ItemNum. Most records have a value for ItemNum. I set Grouping And Sorting to sort ascending on the ItemNum field. The records where ItemNum is Null appear at the...
by: Tim Marshall | last post by:
Access 2003. The situation is this: I have a "criteria selection form" in which there are a many different criteria, mostly displayed as combo and text boxes in which a user can enter stuff,...
by: Jimmy | last post by:
Is there a way to sort/group a report based on the second column of a combo box, i.e. the text associated with the primary key number?
by: sara | last post by:
Hi - I have looked at all posts and tried both Allen Browne's Report Sorting at run Time ( Select Case Forms!frmChooseSort!grpSort Case 1 'Name Me.GroupLevel(0).ControlSource = "LastName"...
by: Gumbyu | last post by:
Hello all, I have been working on this issue for about a week and still cannot get anything to group or sort. At this point, I just want to be able to group a report by using a 'checkbox' on a form....
by: isladogs | last post by:
The next Access Europe User Group meeting will be on Wednesday 3 Apr 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM). In this session, we are pleased to welcome former...
by: Charles Arthur | last post by:
How do i turn on java script on a villaon, callus and itel keypad mobile phone
by: aa123db | last post by:
Variable and constants Use var or let for variables and const fror constants. Var foo ='bar'; Let foo ='bar';const baz ='bar'; Functions function $name$ ($parameters$) { } ...
by: ryjfgjl | last post by:
If we have dozens or hundreds of excel to import into the database, if we use the excel import function provided by database editors such as navicat, it will be extremely tedious and time-consuming...
by: ryjfgjl | last post by:
In our work, we often receive Excel tables with data in the same format. If we want to analyze these data, it can be difficult to analyze them because the data is spread across multiple Excel files...
by: emmanuelkatto | last post by:
Hi All, I am Emmanuel katto from Uganda. I want to ask what challenges you've faced while migrating a website to cloud. Please let me know. Thanks! Emmanuel
by: BarryA | last post by:
What are the essential steps and strategies outlined in the Data Structures and Algorithms (DSA) roadmap for aspiring data scientists? How can individuals effectively utilize this roadmap to progress...
by: Sonnysonu | last post by:
This is the data of csv file 1 2 3 1 2 3 1 2 3 1 2 3 2 3 2 3 3 the lengths should be different i have to store the data by column-wise with in the specific length. suppose the i have to...
by: Hystou | last post by:
There are some requirements for setting up RAID: 1. The motherboard and BIOS support RAID configuration. 2. The motherboard has 2 or more available SATA protocol SSD/HDD slots (including MSATA, M.2...

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.