Display data horizontally using concatenation

I have subtables which list data for each patient such as

Med1
Med2
Med3

When I created the subreports, naturally the meds are listed this way. What
I want to do is have them listed this way to keep my reports from being too
long:

Med1, Med2, Med3

I have tried Duane Hookum's code, but just don't understand enough to get it
working right (eg What's DAO and ADO?). Is there a simple solution... please
help!

Wht not join the tables in a query which feeds your report?
Why do you have separate tables for each med?
Why not have all meds in one table that points to the patient?
-- Dorian
"Give someone a fish and they eat for a day; teach someone to fish and they
eat for a lifetime".


"Dr. Lost" wrote:

I have subtables which list data for each patient such as

Med1
Med2
Med3

When I created the subreports, naturally the meds are listed this way. What
I want to do is have them listed this way to keep my reports from being too
long:

Med1, Med2, Med3

I have tried Duane Hookum's code, but just don't understand enough to get it
working right (eg What's DAO and ADO?). Is there a simple solution... please
help!

Sorry, I wasn't being clear. I have a master patient table NewPatients which
contains an autonumber PatientID which is the primary key. I then have many
linked subtables: MedSubtable, PMHSubtable, FHSubtable, etc which is each
linked by Patient ID. For example, MedSubtable contains PatientID, MedID
(primary key), Med, dose, etc. When I want to generate a report that lists
this patient's history, there are subreports generated from queries using
data from the subtables and some calculated fields. The subreports come out
like this:

Meds:

med1
med2
med3

Past Medical History:

PMH1
PMH2
PMH3

I would like them to appear like this:

Meds:

med1, med2, med3, etc.

Past Medical History:

PMH1, PMH2, PMH3, etc.

I know the solution is probably simple, but it eludes me.



"Dorian" wrote:

Wht not join the tables in a query which feeds your report?
Why do you have separate tables for each med?
Why not have all meds in one table that points to the patient?
-- Dorian
"Give someone a fish and they eat for a day; teach someone to fish and they
eat for a lifetime".


"Dr. Lost" wrote:

I have subtables which list data for each patient such as

Med1
Med2
Med3

When I created the subreports, naturally the meds are listed this way. What
I want to do is have them listed this way to keep my reports from being too
long:

Med1, Med2, Med3

I have tried Duane Hookum's code, but just don't understand enough to get it
working right (eg What's DAO and ADO?). Is there a simple solution... please
help!

On Fri, 19 Dec 2008 13:35:01 -0800, Dr. Lost
wrote:

I would like them to appear like this:

Meds:

med1, med2, med3, etc.

It's not all that trivial, but not very hard. You need some VBA code to return
a concatenated string of comma separated values. See the code and instructions
at
http://www.mvps.org/access/modules/mdl0004.htm
--

John W. Vinson [MVP]

You need to create a function that loops through the list of Meds for the
specific patient and builds a string of all the meds.

Steve



"Dr. Lost" Dr. wrote in message
...
I have subtables which list data for each patient such as

Med1
Med2
Med3

When I created the subreports, naturally the meds are listed this way.
What
I want to do is have them listed this way to keep my reports from being
too
long:

Med1, Med2, Med3

I have tried Duane Hookum's code, but just don't understand enough to get
it
working right (eg What's DAO and ADO?). Is there a simple solution...
please
help!

All you need to do is change the subreport to an 'across then down'
multi-column layout. You'll find an example at:


http://community.netscape.com/n/pfx/...apps&tid=24271


The file also includes a code solution using a single report in which the
layout is amended by code in the report's module at runtime, but the
subreport solution is far easier. I originally produced the file in response
to a reader's enquiry in a magazine column written by a contact of mine,
merely to show that what he was asking could be done, but a subreport is the
best way to do it.

Ken Sheridan
Stafford, England

"Dr. Lost" wrote:

I have subtables which list data for each patient such as

Med1
Med2
Med3

When I created the subreports, naturally the meds are listed this way. What
I want to do is have them listed this way to keep my reports from being too
long:

Med1, Med2, Med3

I have tried Duane Hookum's code, but just don't understand enough to get it
working right (eg What's DAO and ADO?). Is there a simple solution... please
help!