Multi-column report with text from separate fields flowing down each column

[Rabi Tripathi]

Hi folks:
I have a rather challenging (for me, anyway) reporting requirement and
would appreciate any feedback. In short, I am trying to create a multi
column report in which...for each main record, text retrieved from
multiple supporting tables is appended with the right prefixes and
formatted as I want, missing information doesn't get the prefixes and
doesn't show as blank spaces, and then the resulting block of text
wraps and flows to as many lines as necessary. Details below.

I have data in Access (2002) in multiple tables that constitute a
language dictionary. I am trying to figure out ways I can produce a MS
Access report (or a pritable document in any other format such as PDF
or even RTF) that looks exactly like a printed dictionary (possibly
with two columns). I am having a hard time producing such report in
Access because many of the word entries (my main table records) are
missing much of the optional information on supporting tables and
using "Can Shrink" on fields and sections doesn't always give me a
nice report that doesn't show nasty blank spaces/lines...unless I use
a separate line on the report for each textbox which I don't want to
do to conserve space (and dictionaries don't show detailed info on
each word on separate lines; they rather have all info on a word
compacted into one block of text flowing freely into as many lines in
the column as necessary).

As you can imagine, the database has a somewhat large number of tables
with complex relationships and a typical word has information on some
of the tables only, with each word touching on different set of
tables. For example, some words have examples that need to appear on
the report with an "ex." prefix, others don't; some words have
synonyms, others don't; some words need to indicate they are "vulgar"
or "obsolete", others don't. I have found it impossible to design a MS
Access report without odd blank spaces, mainly because Access reports
are designed with absolute positions for each text box, and they don't
allow me to just spit out the text and format information from
multiple sources and have the resulting block "flow" to as many lines
as necessary, wrapping automatically as necessary.

Any ideas? I thought of having a detail section in my report with only
one text box and then using VB to dynamically compute the formatted
block of text, but a text box has only one set of format properties,
so I can't do it. Am I at the end of Access's reporting cababilities?
In that case what other format/product can I use...any way to generate
a RFT or PDF document dynamically?

Incidentally, I recently visited my medical insurance's web site to
get a list of doctors. After I got the list of doctors matching my
criteria, their web site spit out a PDF document that impressively
looked like a printed book with three columns in which information
about each provider nicely flowed from line to line within a column.
Wonder how they translate from a database records to such
multi-column, free flowing text. [No I haven't tried asking the
insurance company how they do it, because they will surely hang up
suspecting industrial espionage or something equally sinister ]

Thanks in advance and regards.

 

[Marshall Barton]

I have a rather challenging (for me, anyway) reporting requirement and
would appreciate any feedback. In short, I am trying to create a multi
column report in which...for each main record, text retrieved from
multiple supporting tables is appended with the right prefixes and
formatted as I want, missing information doesn't get the prefixes and
doesn't show as blank spaces, and then the resulting block of text
wraps and flows to as many lines as necessary. Details below.

I have data in Access (2002) in multiple tables that constitute a
language dictionary. I am trying to figure out ways I can produce a MS
Access report (or a pritable document in any other format such as PDF
or even RTF) that looks exactly like a printed dictionary (possibly
with two columns). I am having a hard time producing such report in
Access because many of the word entries (my main table records) are
missing much of the optional information on supporting tables and
using "Can Shrink" on fields and sections doesn't always give me a
nice report that doesn't show nasty blank spaces/lines...unless I use
a separate line on the report for each textbox which I don't want to
do to conserve space (and dictionaries don't show detailed info on
each word on separate lines; they rather have all info on a word
compacted into one block of text flowing freely into as many lines in
the column as necessary).

As you can imagine, the database has a somewhat large number of tables
with complex relationships and a typical word has information on some
of the tables only, with each word touching on different set of
tables. For example, some words have examples that need to appear on
the report with an "ex." prefix, others don't; some words have
synonyms, others don't; some words need to indicate they are "vulgar"
or "obsolete", others don't. I have found it impossible to design a MS
Access report without odd blank spaces, mainly because Access reports
are designed with absolute positions for each text box, and they don't
allow me to just spit out the text and format information from
multiple sources and have the resulting block "flow" to as many lines
as necessary, wrapping automatically as necessary.

Any ideas? I thought of having a detail section in my report with only
one text box and then using VB to dynamically compute the formatted
block of text, but a text box has only one set of format properties,
so I can't do it. Am I at the end of Access's reporting cababilities?
In that case what other format/product can I use...any way to generate
a RFT or PDF document dynamically?

Incidentally, I recently visited my medical insurance's web site to
get a list of doctors. After I got the list of doctors matching my
criteria, their web site spit out a PDF document that impressively
looked like a printed book with three columns in which information
about each provider nicely flowed from line to line within a column.
Wonder how they translate from a database records to such
multi-column, free flowing text.

You need to create the report's record source query to
retrieve all possible related values. This can be done
using outer joins from your word table to the synonyms,
archaic, etc. tables.

Once the data is available to the report, you can use a
single CanGrow text box with an expression that concatenates
all the pieces together. To include the prefixes only for
the parts that exist, use both concatenation operators & and
+. The + operator will propogate a Null value, but & will
not. For example:

=WordField & " " & DefinitionField & (" Synomyns - " +
SynonymField) & (" Archaic - " + ArchaicField)

I don't know what your'e aiming at with:
"a text box has only one set of
format properties, so I can't do it"

But if you want some words in italics/bold and some not,
then you'll have to use a different kind of control,
probably a Rich Text Format (RTF) control. A free one is
available at www.lebans.com.

 

[Rabi Tripathi]

You need to create the report's record source query to
retrieve all possible related values. This can be done
using outer joins from your word table to the synonyms,
archaic, etc. tables.

Once the data is available to the report, you can use a
single CanGrow text box with an expression that concatenates
all the pieces together. To include the prefixes only for
the parts that exist, use both concatenation operators & and
+. The + operator will propogate a Null value, but & will
not. For example:

=WordField & " " & DefinitionField & (" Synomyns - " +
SynonymField) & (" Archaic - " + ArchaicField)

I don't know what your'e aiming at with:
"a text box has only one set of
format properties, so I can't do it"

But if you want some words in italics/bold and some not,
then you'll have to use a different kind of control,
probably a Rich Text Format (RTF) control. A free one is
available at www.lebans.com.

Thanks a bunch! Using RTF control instead of a regular text box is the
way to go for me, as I need to have different parts of the block of
text composed by the VB code formatted differently. I still have major
work to do because I am rather rusty on VB right now and I need to
make sure the report can be multi-column, but you have taken me past a
major hurdle.
Thanks again!

 

[Rabi Tripathi]

You need to create the report's record source query to
retrieve all possible related values. This can be done
using outer joins from your word table to the synonyms,
archaic, etc. tables.

Once the data is available to the report, you can use a
single CanGrow text box with an expression that concatenates
all the pieces together. To include the prefixes only for
the parts that exist, use both concatenation operators & and
+. The + operator will propogate a Null value, but & will
not. For example:

=WordField & " " & DefinitionField & (" Synomyns - " +
SynonymField) & (" Archaic - " + ArchaicField)

I don't know what your'e aiming at with:
"a text box has only one set of
format properties, so I can't do it"

But if you want some words in italics/bold and some not,
then you'll have to use a different kind of control,
probably a Rich Text Format (RTF) control. A free one is
available at www.lebans.com.

Thanks a bunch! Using RTF control instead of a regular text box is the
way to go for me, as I need to have different parts of the block of
text composed by the VB code formatted differently. I still have major
work to do because I am rather rusty on VB right now and I need to
make sure the report can be multi-column, but you have taken me past a
major hurdle.
Thanks again!