Placing one field from sequential records in sequential order in a report

[Ben McCall]

I have a program in Access 97 that I have been working on to interview my patients before I see them. They are asked multiple questions. The answers to these questions are in one table with one answer per record. I would like to be able to print these in a report in sequential order instead of one answer per line. I have been unable to do this. I am sure that there must be some easy solution to this problem but I have not been able to find it. Any help would be appreciated.

Ben W. McCall

 

[Chris RR]

I'm not entirely sure that I understand what you want to do, but it sounds like rather than seeing:
Answer A.
Answer B.
Answer C.

You want to see:
Answer A. Answer B. Answer C.

To do this, put a text box on your report, give it a name, then set up the control source like this:
=[FIELDA]+ " " + [FIELDB] + " " + [FIELDC]

I'd recommend setting the Can Grow and Can Shrink properties to Yes, for this field and for the Detail section. My example shows putting a space between the fields; you could skip that or use other divider.

 

[Ben McCall]

Thanks!

I do want the report to show:

Answera. Answerb. Answerc.

Each answer is in a separate record. So they are nat answera answerb and answerc but answer from record1 answer from record 2 and answer from record 3. I could concatenate them as you suggested in they were in the same record but I can find no way to do that with fields in different records.

Do you have any other suggestions or I misinterpreting your suggestion?

Ben McCall

 

[dhoffman]

That sounds tricky. I just so happen to be in a very similar predicament, else I don't usually read the Reports section.
I have a database of water samples where tests have been done for the presence of various chemicals in each water sample. I want a report to show the results for one water sample. The chemical data exists in a table where there there IDs identifying the chemical and the sample this data belongs to. I can make all of the chemical records belonging to the sample appear if I use grouping, however there is something that adds the difficulty resulting in my question:
If all of the chemical records will fit on one page in one column then that's the way I want it, straight down the middle of the page. However some samples are tested for a greater number of chemicals, and the data will not all fit on one page in one column. In that case I want the data to appear in two columns to the left and right of the middle.

I have thought of some possible solutions. One would be to create three two fields pairs (one for the compound name, the other for it's concentration) and then count the number of records before opening the form and determining whether to use the middle one or the outside two. But, if I use the outside two pairs, how do I actually do it? The same data will simply be used by each pair. So I don't know if such an idea helps me at all. And I know it doesn't help the guy who started this thread at all.

I don't know much about Report design, I've usually had to do very little with Reports. Maybe some more ideas will come to me if I go read some more.

 

[dhoffman]

About ChrisRR's answer: Is there some way you could do who what you wrote, but while referencing a particular record before referencing each field? For example:
= Record1.[name] & " " & Record1.[value] & " " & Record2.[name] & " " & Record2.[value] & etc.

 

[Ben McCall]

I have tried all form of your suggestion but can find nothing in the books or help file to help me.

Any further ideas?

Ben McCAll

 

[pcs]

you need to look at Page Setup for your report...under the columns tab set it for the number of columns you desire.

to get a quick peek at the settings required, use the Label Wizard and then set up your report the same way.

hth,
al

 

[Pat Hartman]

I think this article from the knowledge base may be what you are looking for:

Q141624 - ACC How to Concatenate a List of Items from a Many-Side Table

 

[Ben McCall]

That article looked good and appeared with modifications that it would accomplish what I was trying to do. I carried it out as written in Northwinds. First I got an error message that I had not declared "FirstPass". I declared it as a variant and received no furhter error messages. When I previewed the report, I had only one product with none of the others listed and separated by commas as promised. Any suggestions? It will be hard to translate it to my needs if I cannat get the original to work.

Ben McCall