UPDATE and ORDER

[Jerry]

Hi,
I have 2 tables: A with Key1, String1, B with Key1, Val1 (sorted by Key1 and
Val1).
Table B has multiple records for each Key1.
Update query:
UPDATE A INNER JOIN B (ON A.Key1=B.Key1) SET A.String1=A.String1 & B.Val1;
works fine except the order by which the values are concatenated into
String1 is in many cases wrong. Can't force the proper order... Can anybody
help?
Jerry

 

[John Vinson]

Hi,
I have 2 tables: A with Key1, String1, B with Key1, Val1 (sorted by Key1 and
Val1).
Table B has multiple records for each Key1.
Update query:
UPDATE A INNER JOIN B (ON A.Key1=B.Key1) SET A.String1=A.String1 & B.Val1;
works fine except the order by which the values are concatenated into
String1 is in many cases wrong. Can't force the proper order... Can anybody
help?
Jerry

I think you'll need to use VBA instead:

http://www.mvps.org/access/modules/mdl0004.htm


John W. Vinson[MVP]

 

[Jerry]

Thanks for your help John...
I know that I can do it using VB, but it looks like there should be a way to
force the
sort of the records in the linked table. This looks like a bug.
What I get is something like this:
Table A records: K1, Null; K2, Null
Table B records: K1, A; K1, B; K1, C; K2, X; K2, Y; K2, Z;
After running my UPADTE query I get this:
K1, ABC; K2, ZXY; instead of
K1, ABD; K2, XYZ;
And there no ORDER BY under UPDATE...
Jerry

 

[John Vinson]

And there no ORDER BY under UPDATE...

Correct. There is no ORDER BY under Update. So you can't do it, AFAIK!

John W. Vinson[MVP]

 

[John Spencer]

I wonder if this would be a place to use Duane Hookom's Concatenate
function?

http://www.rogersaccesslibrary.com/OtherLibraries.asp#Hookom,Duane.

I know that is still using VBA, but the SQL statement would look something
like:

UPDATE A INNER JOIN B
ON A.Key1=B.Key1
SET A.String1 = Concatenate("SELECT B.Key1 FROM B WHERE B.Key1=" & A.Key1 &
" ORDER By B.Val1", "")