How to display data from mulple child records in single report line

  • Thread starter Thread starter Tim Callahan
  • Start date Start date
T

Tim Callahan

Hello,

I have two tables, a parent and a child. THere is a 1 to
many relationship from the parent to the child.

I have a simple report that lists the contents of the
parent table. I want one field to be a concatenation of
data from the related child record(s).

This is currently working by using calling a function e.g.
=GetChildRecordValues([ParentId]) that runs a parameter
query and iterates through the results, and returns a
string with the concatenated child record values.

I suspect that this is a slow process and wanted to know
if there might be a higher performance way of doing this.

Thanks in advance,

Tim
 
It is likely that if you join both tables in a Query and use that Query as
the basis of your report that it will perform better. However, if you are
now concatening the values into a string, it will, of necessity, look
different, because you'll show them in separate lines.

Larry Linson
Microsoft Access MVP
 
Well, there are possibly two ways to do this that are much faster:

1.) Create a sub report which draws the data from the child tables only
(include the PK in the sub report query) and then link the maste report and
child reports by the key between the tables, placing the sub report in the
detail section.

2.) In your query use an outer join between the tables to include all rows
common on the key between the two tables. This will pull in all records
from the child table, and duplicate the data from the main table for each
row. Then for the fields that are duplicated set the hide duplicates value
to true.
 
Tim said:
I have two tables, a parent and a child. THere is a 1 to
many relationship from the parent to the child.

I have a simple report that lists the contents of the
parent table. I want one field to be a concatenation of
data from the related child record(s).

This is currently working by using calling a function e.g.
=GetChildRecordValues([ParentId]) that runs a parameter
query and iterates through the results, and returns a
string with the concatenated child record values.

I suspect that this is a slow process and wanted to know
if there might be a higher performance way of doing this.
Click to expand...

Not to disagree with everyone else, but I like the way
you're doing it better than the alternatives. In general,
reports use so much processing power that you probably won't
notice an delay caused by this operation.

You might be able to speed things up a little by indexing
the table on the field you're using to filter the data (you
probably already have this since the tables are related).
It might also bea little faster to open the recordset using
dbForwardOnly and dbReadOnly.
 
Back
Top