Re: Aggregate string concatenation efficiency problem

Greetings,

Well, one more thought I had was this: In trying to simplify things --
going back to referential integrity/normalization...

you could pull straight forward data from the query into a temp table
and then pull the column5 data (the consultants into another temp table
and associate the tables. Actually, these tables would not be temporary
- they would be fixed tables but the data would be temporary. The idea
here (assuming Access 2002 or greater) is that with the table
relationship you get a little + symbol at the left side of each row in
the primary table which when you click the + it expands the detail table
(which would be the temp table containing the list of consultants).

This way you don't even have to parse/concatenate anything. You have an
automatic list. You could join/relate tempA and tempB on SaleID when in
tempA you have only 1 record for each saleID and in tempB you would have
as many records for a saleID as there were consultants for that saleID.
Now your search feature would look something like this:

Sub Search
Docmd.RunSql "Insert Into tempA Select * From queryWithNoColumn5 Where
criteria = '" & txtCriteria & "'"
Docmd.RunSql "Insert Into tempB Select * From queryWithColumn5 Where
criteria = '" & txtCriteria & "'"
Me.Requery
Me.Refresh
End Sub

So if you have a subform displaying the search results all the user
needs to do to see the related consultants for a sale is to click/expand
the + on a given record. No looping involved at all.

The goal here is 6 lines of code vs 106 lines of code. Or to take it
one step further - you could have 2 subforms instead of doing the
relationship thing

subform1 displays tempA data. subform2 would display tempB data for a
selected record in subformA. When a user selects a record in subform1
then subform2 gets requeried and displays the corresponding consultants
for the selected record.

Rich

*** Sent via Developersdex http://www.developersdex.com ***
.