Concatenate with SQL - Question for Michel Walsh

[dave]

Using Michel Walsh's SQL concatenation method to create a temporary table, I
have the following two queries:

SELECT DISTINCT tblPayments.ClientID, IIF(False, "",Null) AS PaymentsConcat
INTO tblTempPayments
FROM tblPayments;

UPDATE tblTempPayments INNER JOIN tblPayments ON tblTempPayments.ClientID =
tblPayments.ClientID SET tblTempPayments.PaymentsConcat =
(tblTempPayments.PaymentsConcat+Chr(13)+Chr(10)) & (tblPayments.PayDate &
tblPayments.PayAmount & ("|" + tblPayments.Appeal) & ("|" +
tblPayments.Campaign));

Good so far. However, there is no particular order to the concatenation
process. I would like the concatenated payments to appear in descending date
order. Any suggestions?

I usually use Duane Hookom's concatenation function, but it runs too slowly
in this case.

 

[Michel Walsh]

using a (SELECT * FROM tblPayments ORDER BY ... ) as the right part of the
inner join for the update? May not work, though, depending of how the join
is generated (index, pk, etc)


Vanderghast, Access MVP

 

[dave]

Hi Michel,

Thanks for your response. Per your suggestion I tried this:

UPDATE tblTempPayments INNER JOIN [SELECT tblPayments.ClientID,
tblPayments.PayDate, tblPayments.PayAmount, tblPayments.Appeal,
tblPayments.Campaign FROM tblPayments WHERE tblPayments.Paydate Between
#1/1/2007# and #12/31/2008# ORDER BY tblPayments.PayDate DESC]. AS A ON
tblTempPayments.ClientID = A.ClientID SET tblTempPayments.ConcatPayments =
(tblTempPayments.ConcatPayments+Chr(13)+Chr(10)) & (A.PayDate & "|$" &
A.PayAmount & IIF(A.Campaign & "" = "",("|" + A.Appeal)) & ("|" +
A.Campaign));

The WHERE restricts the dates but the ORDER BY has no effect. I tried
indexing the date field to no effect.

BTW, the concatenation order is by the PK of tblPayments. Is there nothing
much I can do here?

 

[Michel Walsh]

There is not much we can do to "defeat" the optimizer, unfortunately. Maybe
using a RIGHT JOIN could kick a different execution plan though. Can also
use a saved query if you don't like the [ ]. syntax for the virtual table.

Vanderghast, Access MVP