Mutliple Text Fields in a Report (1 Viewer)

[Adam Caramon]

Hi,

I'm mostly self-taught, so this might be something that I just missed, or it might be due to the design of the inherited database, but any help would be appreciated.

We have paper forms that are filled out, approximately 20k-30k/year. Each of these paper forms is read electronically by a scanner and the data is dumped into an Access table.

On these paper forms there are 3 different comment-type sections. In Access, these are all set as MEMO fields. For this explanation we'll call them memo1, memo2, memo3. Often times, these memo fields are blank, as people don't always fill them in, which is ok.

However, when trying to create a report that displays the various information that is being captured, I'm running into problems. If I add each of the memo fields to the detail section of a report based on a query, then it displays much like a simple table view.

Example:

[B]Memo1          Memo2          Memo3[/B]
Blah
               Blah
Blah
                              Blah
Blah
               Blah
                              Blah
                              Blah

Whereas I would like it to show up as:

[B]Memo1          Memo2          Memo3[/B]
Blah           Blah           Blah
Blah           Blah           Blah
Blah                          Blah

In other words, skip blank entries in each text field.

Now, with only 1 text field on a report, you can filter blanks out with criteria in a query, or do the "Can Shrink" trick. But, when you have 3 different text fields from the same table, what's the best way to solve this problem?

Thanks in advance.

 

[DCrake]

What relationship exists between the memo fields and the record? Are you employing any groupings in your report?

 

[Adam Caramon]

What relationship exists between the memo fields and the record? Are you employing any groupings in your report?

Well, each record comes from the same table. The table has several fields, 3 of which are memo1, memo2, memo3 (they each track different things, so it is not an issue of normalization).

I am using some grouping, but that can be changed. Any ideas you have I'll try to work with.

 

[DCrake]

Reason for asking is that if you got it to display as you wish how would you tell one record from another. all you would see is a bunch of text blocks that do not relate to their associated test to the left or right of themselves.

M1 M2 M3
A B C
D E
F G
H

Becomes

A B C
F D E
H G

 

[Adam Caramon]

Reason for asking is that if you got it to display as you wish how would you tell one record from another. all you would see is a bunch of text blocks that do not relate to their associated test to the left or right of themselves.

The way the report is currently set up is basically like this (I've changed Memo1, Memo2, Memo3 to their actual titles to make it more clear, I hope):

[B]Meeting Date Header[/B]
          txtMeetingName
          txtMeetingDate
          txtParticipantCount (Count*)
 
lblComments         lblFutureTopics       lblChangesinPractice
[B]Detail[/B]
[Comments]          [FutureTopics]         [ChangesinPractice]

The Meeting Name & Meeting Date are the same across all records (because they are both query parameters). Each of the memo fields are text that the participants have wrote in. The order does not matter as long as they remain in the same column. In other words, I can't have the text from Future Topics showing up under Comments.


Also, based on your above answer, and what I have found searching the web, the answer might be "You can't do that." If that's the case, any other suggestions for accomodating such a report would be appreciated.

 

[DCrake]

What you may need to do is to add 3 sub reports each one in the detail section and each one containing the relevant memo field and the matching parent data so you can relate the detail to the header.

 

[Adam Caramon]

What you may need to do is to add 3 sub reports each one in the detail section and each one containing the relevant memo field and the matching parent data so you can relate the detail to the header.

Yeah. I ended up having to use 3 sub reports.

Thanks very much for your help.