UPDATE and ORDER

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

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
 
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
Click to expand...

I think you'll need to use VBA instead:

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


John W. Vinson[MVP]
 
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
 
Back
Top