Combining child records

[DC]

I used the code in Microsoft Knowledge Base Article -
322813 to combine child records in my database. It does
the job but a couple of issues have cropped up that maybe
someone can help me with. As the number of records has
grown I notice that when I scroll through records in the
query output, the records are "repainted" on the screen.
And when I split my database into FE/BE the response time
gets really bad at only 200 records. Any ideas?

 

[Allen Browne]

The article uses a user-defined function (UDF) call.
UDFs are always slow (well, nearly always), but in the recent versions they
run 10 times slower than they used to when called in a query.

Would it be possible to move the function call into the Control Source of a
text box on your form or report? If you don't need to sort or filter on the
calcualated field, that may be faster, though you will probably still see
the screen updating each row.

 

[DC]

Allen,
Unfortunately I do need to filter on the value. Do you
know of other methods to combine child records that are
more efficient? I've even considered updating the combined
records to my parent table for easier use in my queries.
My VBA skills are pretty limited(hence my using others
code) so if you can offer some pointers I really
appreciate it.

 

[Allen Browne]

You can use a subquery in the WHERE clause of your query, without making it
read only. The subquery can read the related records.

The WHERE clause of your query will look something like this:

WHERE 'Jones' IN
(SELECT Surname FROM MyRelatedTable
WHERE MyRelatedTable.MyForeignKey = MyMainTable.MyPrimary Key)

More info on subqueries:

http://support.microsoft.com/default.aspx?scid=kb;en-us;209066&Product=acc2000