Concatenate field data on two parameters

  • Thread starter Thread starter Robert Neville
  • Start date Start date
R

Robert Neville

I want to create a report that includes contact names grouped by
JobTitle ROW HEADERS; as oppose to using column header. The report
would help me consolidate information for placement on as a
sub-report. The following data serves as an example.

Sales Representative: John Smith - Larry Johnson - Nancy Davolio -
Janet Leverling - Margaret Peacock - Suyama Michael - Robert King
Vice President, Sales: Andrew Fuller
Sales Manager: Steven Buchanan - Anne Dodsworth
Inside Sales Coordinator: Laura Callahan

Sales Representatives would be the Job Title label; and the hyphen
would delimiter the Contact Name (FirstName & " " & LastName)

A query in conjunction with VBA code may accomplish my objective. I
have been experimenting with several approaches. You may review the
code at the bottom of this post and the SQL follow.

The SQL statement below works, yet I would appreciate your thoughts on
their efficiency. The SQL executes in 60 second for 3549 records.

SELECT First(TEMP_qryContProj.ProjID) AS ProjID,
First(TEMP_qryContProj.JobTitle) AS JobTitle,
StringList("[ContactName]","[TEMP_qryContProj]","[ProjID] =" &
[ProjID] & "AND [JobTitle]='" & [JobTitle] & "'"," - ") AS
ContactNames
FROM TEMP_qryContProj
GROUP BY TEMP_qryContProj.ProjID,
StringList("[ContactName]","[TEMP_qryContProj]","[ProjID] =" &
[ProjID] & "AND [JobTitle]='" & [JobTitle] & "'"," - ")
ORDER BY First(TEMP_qryContProj.ProjID);

This SQL executes faster, yet returns duplicate for each ProjID and
Jobtitle. The duplicates repeat for each concatenated contact return
by the function.

SELECT TEMP_qryContProj.ProjID AS ProjID, TEMP_qryContProj.JobTitle AS
JobTitle, StringList("[ContactName]","[TEMP_qryContProj]","[ProjID] ="
& [ProjID] & "AND [JobTitle]='" & [JobTitle] & "'"," - ") AS
ContactNames
FROM TEMP_qryContProj
ORDER BY TEMP_qryContProj.ProjID;

Please let me know how I could improve my approach since the query
execute too slow for a report with many sub-reports.

'************ Code Start **********
Public Function StringList(varColumns, varTable, _
Optional strWhere As String, Optional strDelimiter = "
- ")
' StringList("[ContactName]","[TEMP_qryContProj]","[ProjID] =" &
[ProjID] & "AND [JobTitle]='" & [JobTitle] & "'"," - ")

Dim dbs As Database
Dim rst As Recordset
Dim fldLoop As Field
Dim strSQL As String

Dim strLineOut As String

Const cstrProc As String = "StringList"

On Error GoTo StringList_Err

' Return reference to current database.
Set dbs = CurrentDb

If strWhere = "" Then strWhere = "true"

strSQL = "Select " & varColumns & " From " & varTable
strSQL = strSQL & " Where " & strWhere
' Debug.Print strSQL
' Exit Function
Set rst = dbs.OpenRecordset(strSQL)

rst.MoveFirst
Do Until rst.EOF
With rst
For Each fldLoop In .Fields
strLineOut = strLineOut & fldLoop.Value & strDelimiter
Next fldLoop
End With
rst.MoveNext
Loop

StringList = Left(strLineOut, Len(strLineOut) - Len(strDelimiter))

StringList_Exit:
Set rst = Nothing
Set dbs = Nothing
Exit Function

StringList_Err:
Call ErrMsgStd(mcstrMod & "." & cstrProc, Err.Number,
Err.Description, True)
Resume StringList_Exit

End Function
'************ Code End **********
 
Including the one you have, there are two additional ways
I might attack your problem. Both of these methods will
be faster, and I call these inline functions. (I've
posted my op ed pages on this a few times.)

The first, probably best, and definitely easiest, solution
is to allow a *subreport* to build your concatenated
string. Why is it easiest? It's easiest because
resetting static variables is very easy with subreports.

On the _OnFormat report event in my example below, I
simply set bStaticReset = True to cause the function
return string to reset (bStaticReset is a global variable).

Then, the recordsource of the subreport calls this
function and returns the concatenated "internal contacts"
(e.g., "James Smith - Larry Bloom - Jane Hill") as an
aggregate result (e.g., Select PMActivityID, Last
(PresenterString([PMActivityID], [InternalContact]))
FROM ... Group By PMActivityID).

Function PresenterString(lngPMActivityID As Long,
strInternalContact As String) As String
On Error GoTo Err_PresenterString

Static lngHoldPMActivityID As Long
Static strPresenterString As String

' determine if to reset the presenter string
If bStaticReset Or lngPMActivityID <>
lngHoldPMActivityID Then
bStaticReset = False
lngHoldPMActivityID = lngPMActivityID

' reset the presenter string
strPresenterString = strInternalContact
Else
' build the presenter string
strPresenterString = strPresenterString & " - " &
strInternalContact
End If

' return the result
PresenterString = strPresenterString

Exit_PresenterString:
Exit Function

Err_PresenterString:
ErrorHandler "modMiscFunctions", "PresenterString",
Err.Number, Err.Description
Resume Exit_PresenterString

End Function


The other component here is it resets when it encounters a
new Group ID (i.e., the child link to the subreport).
However, this is only used when the construction is that
of a "true inline function."

----- Below is my blurb on inline functions. Note that I
consider your current solution to fall within the category
of "Non-Inline Functions."

Version 2: Functions

There are two distinct ways to utilize functions. I break
them down into (a) inline functions and (b) non-inline
functions, or in other words: (a) hard but blazingly fast
and (b) somewhat easy and flexible but slow.

2(a) Inline functions take the current value of the
record, perform a mathematic (or other) function, "stores"
the result of calculation, and return the answer for
display.

Inline functions use the "stored" value for the next
call. Inline functions can be used in Queries, Forms, and
Reports.

Advantages:
--Absolute performance screamers. Inline functions are
hands-down the fastest executors.
--Able to easily handle the largest record counts; linear
time requirement growth

Disadvantages:
--Hardest to manage*
--Is best used in conjunction with Aggregate functions,
such as Last, with or without Group By.**
--Not designed for scrolling, but you can do it if you
really have to***

*Inline functions require the additional requirement of
resetting the "stored" value from execution to exection.
Within queries, this can be achieved by having one query
return the first record, and if the record being executed
is the first record, then it resets the stored value. For
reports, it can be reset with the Report_Open. For forms,
don't use inline functions -- use non-inline functions or
queries.

**Inline functions are superior at returning an aggregate
result based upon line-by-line analysis in an incredibly
fast manner (quite possibly unbeatable speed-wise, even
when compared against code using cursors). Essentially,
you create a query that uses the inline function. Then
you create another query that performs the aggregation
against the first query.

If function execution order is important, there's an
important caveat. The query that runs the inline function
must be based upon a query that includes the SELECT TOP
100 PERCENT ... keywords. "TOP 100 PERCENT" ensures that
your stated Order By is performed against the subquery.
This ensures the inline function is called in the exact
order you demand (such as forcing groups to execute in
order). Then an aggregate query can simply take
the "last" of the inline query to return the blitzkrieg
mathematic results.

Note: Cumulative multiplication in conjunction with
aggregate functions does not require a function. You can
use an expession similar to this one: IIf(Sum(IIf([Value]
<0,1,0)) Mod 2=1,-1,1)*Sgn(Min(Abs([Value])))*Exp(Sum(Log
(Abs(IIf([Value]=0,1,[Value])))))

***While technically the results can be set to be
updatable, it just doesn't work. This is because the
function is called as the user scrolls through the
recordset for the row the user is on. This wreaks
absolute havok with "stored" variables, and will return
unexpected values. If you are going to scroll the
results, you *should* set the result type to Snapshot.

If in-line functions must be used with a scrollable and
updatable recordset, then you must implement methods that
dramatically hamper performance. This is because you have
to forget about using stored values, and dynamically
calculate the proper result based upon the users position
within the recordset.

Here's an example of an in-line function. This function
performs a cumulative multiply, and resets the value when
a new CoGroupID is passed. (In the real world, this
should be performed using the expression I listed above;
this is for illustration only.)

Function InlineMultiply(lngInlineValue As Long,
bInitialize As Boolean, lngCoGroupID As Long) As Long
On Error GoTo Err_Overflow

Static lngHoldValue As Long
Static lngHoldCoGroup As Long
Static lngHoldCompanyID As Long

' determine if to initialize
If bInitialize Then
lngHoldValue = 1
lngHoldCoGroup = lngCoGroupID
lngHoldCompanyID = 0
End If

' determine if a different co group passed (requires
resetting)
If lngHoldCoGroup <> lngCoGroupID Then
lngHoldValue = 1
lngHoldCoGroup = lngCoGroupID
End If

' return the hold value * inline value
lngHoldValue = lngHoldValue * lngInlineValue
InlineMultiply = lngHoldValue

Exit_InlineMultiply:
Exit Function

Err_Overflow:
InlineMultiply = 0
lngHoldValue = 1
lngHoldCoGroup = 0
Resume Exit_InlineMultiply

End Function

The first query involved utilizes the TOP 100 PERCENT
keywords to force the appropriate execution order of the
function. Otherwise, there would be no guarantee that all
CoGroupID 1's would execute before any CoGroupID 2's were
passed (thus resetting the value prematurely).

SELECT TOP 100 PERCENT tblCompanies.*
FROM tblCompanies
WHERE tblCompanies.Value Is Not Null
ORDER BY tblCompanies.CoGroupID, tblCompanies.CompanyName;
|| note this Order By CoGroupID
|| in conjunction with TOP 100 PERCENT

--This query forces the Order By to be performed, thanks
to the TOP 100 PERCENT keywords. This is then used as a
subquery in the next query that actually calls the inline
function:

SELECT qrySortedCompanyData.CompanyID,
qrySortedCompanyData.CoGroupID,
qrySortedCompanyData.CompanyName,
qrySortedCompanyData.Value, InlineMultiply([Value],
[qrySortedCompanyData].[CompanyID]=[qryFirstCompany].
[CompanyID],[qrySortedCompanyData].[CoGroupID]) AS
TheInline
FROM qrySortedCompanyData, qryFirstCompany
ORDER BY qrySortedCompanyData.CoGroupID,
qrySortedCompanyData.CompanyName;

--This query can then be used to make summaries:

SELECT qryRunningMultiply.CoGroupID, Last
(qryRunningMultiply.TheInline) AS LastValue
FROM qryRunningMultiply
GROUP BY qryRunningMultiply.CoGroupID;

-- This summary shows the cumulative value for each group.

2(b) Non-Inline Functions.

I consider your current construction to be that of a "non-
inline function." As you mentioned, you have encountered
both its strengths and weaknesses.
 
Back
Top