[AccessD] How to control an Update Query

Hi Gustav,
Thanks for showing interest in this minor problem here, and also for your
responses to my date field questions some weeks past....

Well, it was about how do you control the sorting when updating a memofield
with several records from a source table, one line in the memofield for each
record.

I gave it another thought and found a solution.

What I first did was :

UPDATE ztblMerge INNER JOIN qztblSource ON ztblMerge.UFN = qztblSource.UFN
SET ztblMerge.MergeField =
IIf(Len([ztblMerge].[MergeField])>0,[ztblMerge].[MergeField] & "
" & [qztblSource].[TextField],[qztblSource].[TextField]),
qztblSource.SelectFlag = Yes
WHERE (((qztblSource.SelectFlag)=No));

...thinking that since the query qztblSource was sorted the way I wanted,
then the records would be written into the merge memofield in the same
sorted way.
They don't!

Creating another select query based on the inner join above and doing the
sort on the fields as required and then using this new select query as
source for the update does the trick:

qzUpdateztblMergeVersion2_Source:
SELECT ztblMerge.UFN, ztblMerge.MergeField, qztblSource.UFN,
qztblSource.TextField, qztblSource.SelectFlag
FROM qztblSource INNER JOIN ztblMerge ON qztblSource.UFN = ztblMerge.UFN
ORDER BY qztblSource.UFN DESC , qztblSource.TextField DESC;

The final update query:
UPDATE qzUpdateztblMergeVersion2_Source AS Q SET Q.MergeField =
IIf(Len([Q].[MergeField])>0,[Q].[MergeField] & "
" & [Q].[TextField],[Q].[TextField]), Q.SelectFlag = Yes
WHERE (((Q.SelectFlag)=No));

Perhaps there is a simpler way?

Borge


----- Original Message ----- 
From: "Gustav Brock" <Gustav at cactus.dk>
To: <accessd at databaseadvisors.com>
Sent: Tuesday, December 21, 2004 8:36 PM
Subject: Re: [AccessD] How to control an Update Query


> Hi Borge
> Oh, they completely confused me.
> Now, what was your question?
> /gustav

> Gustav, they are just visual delimeters added to the data placed in the
> memo
> field... pretty ugly ehh?
> Borge

> > Hi Borge
> > What are the ">>" signs supposed to do?
> > /gustav



---
Outgoing mail is certified Virus Free by AVG Anti Virus System.
Checked by AVG anti-virus system (http://www.grisoft.com).
Version: 6.0.799 / Virus Database: 543 - Release Date: 20/11/2004