By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
424,983 Members | 2,082 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 424,983 IT Pros & Developers. It's quick & easy.

Can you create an updatable query with multible joins

P: n/a
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

Nov 13 '05 #1
Share this Question
Share on Google+
4 Replies


P: n/a
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" <ad****@programconsultants.com> wrote in message
news:11**********************@g44g2000cwa.googlegr oups.com...
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

Nov 13 '05 #2

P: n/a
"Larry Linson" <bo*****@localhost.not> wrote in
news:mRrse.4027$kj5.950@trnddc03:
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.


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
Nov 13 '05 #3

P: n/a
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 ***
Nov 13 '05 #4

P: n/a


Adolph Dupre wrote:
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 ***


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

Nov 13 '05 #5

This discussion thread is closed

Replies have been disabled for this discussion.