0

I coulnd't find a solution for my problem anywhere.

I have two tables "x" and "y". I want to update field x.2 with the records from y.2 by joining x.1 and y.1. The problem is that my y.2 has multiple different records on same y.1.

Now when I try to update x.2 fields with y.2 by joining x.1 and y.1 I only get the first occurring record of y.2 multiple times to field x.2.

How do I get those different values of y.2 to be updated to table "x"?

Before the Update Query:

enter image description here

After Update Query:

enter image description here

Desired Result:

enter image description here

Edper
  • 9,144
  • 1
  • 27
  • 46
AkAntA
  • 13
  • 4
  • 1
    I would [create a unique index](https://stackoverflow.com/questions/2127698/can-we-create-multicolumn-unique-indexes-on-ms-access-databases) on 2 x.1 and x.2. Otherwise, I don't rely see how to do this in SQL. – marlan Mar 13 '18 at 07:31
  • @marlan I have empty fields in table x, so creating unique indexes for those columns is impossible – AkAntA Mar 13 '18 at 08:06
  • Please explain how these rows should be joined without referring to the position of the row (because without an index, rows don't have a position). Why should column 2 of the first row of x be ppp, and not pyt? Current;y. there's no way to differentiate the first, second and third row of table x, and rows that can't be differentiated shouldn't get set to different results. – Erik A Mar 13 '18 at 08:16
  • @ErikvonAsmuth well in my case it doesn't matter whether column 2 of the first row of x is ppp or pyt as long as they all are not the same, but even when I have indexes on columns x.1 and y.1 or x.2 and y.2 it won't work. I might be doing something wrong since I'm not that familiar with indexing – AkAntA Mar 13 '18 at 08:28
  • 2
    @AkAntA It makes all the difference. You can't program Access SQL to do things at random. You need to be able to differentiate. If you add a differentiating factor (e.g. an autonumber field), the problem is easier to solve. – Erik A Mar 13 '18 at 08:31
  • @ErikvonAsmuth thank you for the clearance. I was hoping too much from Access. I solved the problem by adding suggested autonumber field (though with a little bit of hassle but that's just because of the database). – AkAntA Mar 13 '18 at 09:33

0 Answers0