Re: Aggregate string concatenation efficiency problem

On Jan 30, 8:58 pm, Rich P <rpng...@xxxxxxx> wrote:
Hi John,

Your problem is interesting to me - challenging (I hate these kinds of
problems :). I would still go with the idea of getting multiple rows of
consultants in the resultset -- and then do the string concatenation.

Sub DisplaySearchResult()
Dim RS As DAO.Recordset, str1 As String
'--in this sample your query has a normalized column5
'--and say it returns 3 rows -- 3 consultants for the
'--given customer - save resultset to a temp table
'--which you have already created - is persistent
DoCmd.RunSql "Insert Into temp Select * from yourQuery Where..."
Set RS = CurrentDB.OpenRecordset("Select * From temp")
'--for all 3 rows column1 will be 'Dave Smith'
'--no need to iterate here
column1 = RS!Colulmn1
..
column4 = RS!column4
'--now we iterate for each of the consultants
Do While Not RS.EOF
str1 = str1 & RS!Column5 & ", "
RS.MoveNext
Loop
RS.Close
column5 = str1
End Sub

This way you are looping against the resultset instead of the source
data. The resultset should be much smaller than the source data.

Rich

*** Sent via Developersdexhttp://www.developersdex.com***

Rich,

That is a good idea, and preferential to processing the entire table
every time a search is done. It brings us back, however, to the
unpalatable alternative of making exceptions in my search code for
different objects (Select Cases). But I think that's probably my best
option at this point.

In a true search form, elsewhere in my application, I already solved
this very problem, and could customize the way the data was returned
because the search form itself was specific to the object being
searched. Here's a slightly hacked but I think fairly efficient way
to do it: (air code to follow)

Dim SalesUB as Long
Dim SalesLB as Long

set rs=db.openrecordset("SELECT SaleID FROM Sales ORDER BY SaleID;)
SalesLB=rs!SaleID
rs.movelast
SalesUB=rs!SaleID
rs.close

Dim aHits() as Byte
redim aHits(SalesLB to SalesUB)

Dim aItemText() as Long
redim aItemText(SalesLB to SalesUB)

'My query will return normalized data, i.e. 1 record for each
consultant on the sale
set rs=db.openrecordset([My query])

dim SaleID as long
Do until rs.EOF
SaleID =rs!SaleID
If aHits(SaleID )=0 Then
aHits(SaleID)=1

ItemTextDelimmed = vbNullString
For n = 1 To ColumnCount
ItemTextDelimmed = ItemTextDelimmed & Nz(rs("Column" & n)) &
"@"
Next
ItemTextDelimmed = Left(ItemTextDelimmed, Len(ItemTextDelimmed) -
1)

aItemText(SaleID)=ItemTextDelimmed
OpenArgsArrayString.Append rs!VBAOpenArgs & "|"
Else
'Code to append additional consultant to ItemTextDelimmed entry
in the array
End if
rs.movenext
Loop

'Code to loop through aItemText and create my ItemTextArrayString
object, ignoring empty array space

By way of explanation:
In small installations (less than millions of records) it's fairly
economical to create an array with every possible primary key from the
table you want to check 'duplicates' against (duplicates in this case
are multiple SaleIDs for each ConsultantSale). Then, to check if a
SaleID has already been added to the final data (you're seeing another
record with another Consultant for the Sale), you merely check the
byte array aHits to see if the value for that slot has been changed to
a 1. If it has, you append Column5 to the aItemText array. Finally I
can walk the aItemText array to pull out all non-blank entries and
create my ItemTextArrayString object.

I suppose that this is what I'll end up doing, even though it's
slightly messy and even though it will require me to break the mold I
created for this QuickSearch function.

Any thoughts?
.

Relevant Pages

  • Re: Detecting a running process.
    ... Private Declare Function EnumProcessModules Lib "psapi.dll" _ ... (ByVal dwProcessID As Long, _ ... Dim nProcesses As Long ... 'fill an array of longs with the ...
    (microsoft.public.vb.winapi)
  • Re: Max/Min Functions
    ... You said the Array function won't work here. ... Dim myArray() As Variant ... > Dim vMax As Variant ...
    (microsoft.public.word.vba.general)
  • Morefunc VSort with array of long output
    ... I picked up a really neat trick from Jim Mack to alter the lBound of an array. ... Dim VType As Integer ... 'Will be about 4 to 5 times faster than a quicksort and can sort ...
    (microsoft.public.excel.programming)
  • Re: Spelling Error Fequency
    ... > Dim arrSpErrorsAs String 'Array to hold unique misspelled ... > Dim tempString As String ... >>> identical misspellings the array only contains one instance of the ... >>> Dim oSpError As Object ...
    (microsoft.public.word.vba.general)
  • Re: Spelling Error Fequency
    ... Dim arrSpErrorsAs String 'Array to hold unique misspelled ... Dim tempString As String ... >> identical misspellings the array only contains one instance of the ... >> Dim oSpError As Object ...
    (microsoft.public.word.vba.general)