ORDER BY in UPDATE

[christianlott1]

UPDATE CLPROV_RANKED INNER JOIN Clean_Lqcn ON CLPROV_RANKED.PHONE =
Clean_Lqcn.Phoney SET Clean_Lqcn.PROV_ID = [SUBMID], Clean_Lqcn.
[Match] = "D", Clean_Lqcn.Class = "C", Clean_Lqcn.Rank = [Result]
WHERE (((CLPROV_RANKED.SUBMID) Like "CL" & "*") AND
(((LCS((UCase(NZ([PROV_ADDR2]," "))) & " " & (UCase(NZ([PROV_ADDR3],"
"))),(UCase(nz([DESCR1]," "))))>[Result]))=True));

I need to put an ORDER BY:

ORDER BY clprov4.Rank;

or

FROM clprov4
ORDER BY clprov4.Rank;

Access keeps giving me errors when I paste in the order by clause.


Christian

 

[Amy Blankenship]

What use is an Order By in an Update Clause? The join field is either equal
or it's not, and reordering the fields won't change that.

 

[George Nicholson]

UPDATE queries are Action queries. They act upon a set of records, they do
not return records. OrderBy clauses only apply if records are being returned
(a SELECT query, etc.). OrderBy is neither expected or accepted within an
Action query.

HTH,

 

[christianlott1]

What use is an Order By in an Update Clause? The join field is either equal
or it's not, and reordering the fields won't change that.

eeeyah...

I know what I wanted but thinking this over, I think I'm going to have
to break this problem down into one more step...

Thanks,

Christian

 

[christianlott1]

I can figure out a procedural (setting up an array) way to do this but
I was wondering if there was a declarative approach I was missing.

I have an blank ID field I need to match up from a reference table
based on a set of criteria (the filters on the other fields).

There can be multiple matches but there is a RANK field that's from
0-9 currently, it may go higher than that. I need to take the first
best match from Rank Zero. If there is no matching zero rank, it needs
to take the next match, which may be Rank 2 or 3 etc.. in that order.

So you see, it would help be able to sort because the query is simply
taking the first value it sees in the Reference file and plugging it
into the ID field.

Or maybe it's plugging in all the numbers but I'm only left with the
last it plugged in. It doesn't matter. If I was able to sort Ascending/
Descending I could get it to come out right.

I know this query works as it is, it's just that I don't get sorted
matches.

I could have Bill #2 matched but there's a Bill #1 also in the
reference file. There could be Bill #0-29, but I only wanted to match
and update the ID to the one from Bill #0.

Thanks,

Christian