It appears that Jet treats the "many" side of the join as a "Top 1" type
query in your situation, where the sort order is not defined and thus Jet
will select the first record it "gets" from the "many" side. If you have
multiple records in T2 with same value of t1, how would you decide which
one
to use for the update if you were doing it manually? We'll need to tell
ACCESS how to make that same decision.
Provide some more information about what you want to do, and we can
suggest
a change to the query.
--
Ken Snell
<MS ACCESS MVP>
newgun said:
Thanks for replying.
Yes I tried. It allowed me to do it (surprise!), that's why I asked
here
because it shouldn't. With the simple tables I had, it used the first
record
(I have 3 records in T2 with the same value of t1 as in T1, it used
the
first t2 value of the three, but I'm not sure that's the norm). I was
using
Access 2000.
:
Did you try it on a copy of your database? My guess is that ACCESS
will
tell
you it cannot run this query because it cannot identify the
record/table
correctly.
What type of data are in both tables? Is there just a single record in
T2
with the same value of t1 as in T1?
--
Ken Snell
<MS ACCESS MVP>
If I have
UPDATE T1 INNER JOIN T2 ON T1.t1 = T2.t1 SET T1.t2 = [T2]![t2];
and T2 returns multiple rows on the inner join, which row's t2 value
Access
will use to do the update? The first? Last? Or Access will choose
randomly?