Can you create an updatable query with multible joins

I created 2 tables, each with an autonumber primary key. Fields are:
ID (autonumber Primary key)
Number (single)
Color (Text)
FName (text)in one table and LName (text)in the other

What I'm trying to do is make a select query with not one but two
fields in the join, that will allow me to update the LName field . IE

SELECT FirstName.FName, LastName.LName, FirstName.ID, FirstName.Number,
LastName.ID, LastName.Number
FROM LastName INNER JOIN FirstName ON (LastName.Number =
FirstName.Number) AND (LastName.ID = FirstName.ID);

I've tried:
adding all the joined/keyed/indexed fields
changing the inner joins to left then right joins.
Indexing the number field with no duplicates

Once I add the second join, the query is no longer updatable. Any
comments are welcome.
Thanks in advance,
Adolph

re: Can you create an updatable query with multible joins

You can't _always_ make a multijoin query updateable, but sometimes you can.
Make sure the primary key field of the table(s) you want to update are
contained in the query... without that, you are doomed from the beginning.

Larry Linson
Microsoft Access MVP



"adolph" <adolph@programconsultants.com> wrote in message
news:1118951831.605366.240400@g44g2000cwa.googlegr oups.com...[color=blue]
> I created 2 tables, each with an autonumber primary key. Fields are:
> ID (autonumber Primary key)
> Number (single)
> Color (Text)
> FName (text)in one table and LName (text)in the other
>
> What I'm trying to do is make a select query with not one but two
> fields in the join, that will allow me to update the LName field . IE
>
> SELECT FirstName.FName, LastName.LName, FirstName.ID, FirstName.Number,
> LastName.ID, LastName.Number
> FROM LastName INNER JOIN FirstName ON (LastName.Number =
> FirstName.Number) AND (LastName.ID = FirstName.ID);
>
> I've tried:
> adding all the joined/keyed/indexed fields
> changing the inner joins to left then right joins.
> Indexing the number field with no duplicates
>
> Once I add the second join, the query is no longer updatable. Any
> comments are welcome.
> Thanks in advance,
> Adolph
>[/color]

re: Can you create an updatable query with multible joins

"Larry Linson" <bouncer@localhost.not> wrote in
news:mRrse.4027$kj5.950@trnddc03:
[color=blue]
> You can't _always_ make a multijoin query updateable, but
> sometimes you can. Make sure the primary key field of the table(s)
> you want to update are contained in the query... without that, you
> are doomed from the beginning.[/color]

Also, the Access-specific DISTINCTROW predicate can often work
wonders, at least for some kinds of queries.

--
David W. Fenton http://www.bway.net/~dfenton
dfenton at bway dot net http://www.bway.net/~dfassoc

re: Can you create an updatable query with multible joins

I've My query already has:
1. the keyed field as a join
2. another joined field that is indexed
3. All joined fields (keyed and indexed) from both tables in the
results.

Yet the query is not updatable.
Any ideas? Or can you give a simple example of a multijoined query that
is updatable so I can compare it with mine.
Adolph


*** Sent via Developersdex http://www.developersdex.com ***

re: Can you create an updatable query with multible joins

Adolph Dupre wrote:[color=blue]
> I've My query already has:
> 1. the keyed field as a join
> 2. another joined field that is indexed
> 3. All joined fields (keyed and indexed) from both tables in the
> results.
>
> Yet the query is not updatable.
> Any ideas? Or can you give a simple example of a multijoined query that
> is updatable so I can compare it with mine.
> Adolph
>
>
> *** Sent via Developersdex http://www.developersdex.com ***[/color]

I've had the exact situation happen to me. Fortunately, when I
replaced a join with the IN (SELECT ... ) syntax the query became
updateable. YMMV.

James A. Fortune