Are select queries with inner joins updatable?

[Martin Lacoste]

Access 2000
Trying even the most basic queries - select queries joining (inner
join) two tables - and the results are not updatable, and I can't tell
why they aren't. All permissions are on, recordset is dynaset, unique
values is no, etc.. etc..

Here's the SQL if it helps..:

SELECT tbl.ID
FROM tbl INNER JOIN Master ON tbl.IDNumber = Master.IDNumber ;

Should these results not be updatable? Ideas? Thanks!
Martin Lacoste

[Jim Allensworth]

On 2 Jul 2004 14:41:30 -0700, ma*******@roger s.com (Martin Lacoste)
wrote:

Access 2000
Trying even the most basic queries - select queries joining (inner
join) two tables - and the results are not updatable, and I can't tell
why they aren't. All permissions are on, recordset is dynaset, unique
values is no, etc.. etc..

Here's the SQL if it helps..:

SELECT tbl.ID
FROM tbl INNER JOIN Master ON tbl.IDNumber = Master.IDNumber ;

Should these results not be updatable? Ideas? Thanks!
Martin Lacoste

Try putting the join key of both tables in the query.

SELECT tbl.IDNumber, Master.IDNumber
FROM tbl INNER JOIN Master ON tbl.IDNumber = Master.IDNumber ;
- Jim

[DCM Fan]

Two things: 1) Make sure the tables have primary keys, and 2) make sure you
have the latest JET update.

[Jim Allensworth]

On 03 Jul 2004 00:50:36 GMT, dc****@aol.comSPNOAM (DCM Fan) wrote:

Two things: 1) Make sure the tables have primary keys, and 2) make sure you
have the latest JET update.

Well, duh!
What made you come up with those observations/suggestions?

[DCM Fan]

<<What made you come up with those observations/suggestions?>>

10 years of answering questions on this board, obviously posted by novices.

Here are a few more, if those suggestions offended you. Are you joining on the
keyed fields? Are these tables LINKED from a different backend DB than JET? Are
tehre any extraneous properties applied, like filters or sorting?

[Jim Allensworth]

On 03 Jul 2004 05:09:17 GMT, dc****@aol.comSPNOAM (DCM Fan) wrote:

<<What made you come up with those observations/suggestions?>>

10 years of answering questions on this board, obviously posted by novices.
Ooh. Now wer'e all puffed up.
Here are a few more, if those suggestions offended you. Are you joining on the
keyed fields? Are these tables LINKED from a different backend DB than JET? Are
tehre any extraneous properties applied, like filters or sorting?

I wasn't offended, it's just that your suggestions - to my posting -
were unnecessary. Just noise and therefore potentially confusing.

The OP wanted to know why an INNER JOIN wasn't updateable. I provided
what I considered a succinct - and helpful - response which, IME, is a
better approach to problem solving. Just throwing stuff at it isn't
particularly productive.

For example you stated:

Two things: 1) Make sure the tables have primary keys, and 2) make sure you
have the latest JET update.

For the problem presented 1) is not relevant - primary keys, per se,
have no bearing on whether the recordset can be updated or appended.

As for 2); well, yes, you should *always* make sure you have the
latest updates - Jet or otherwise. You could tag that onto every post
or put it in your sig.

Regards,
- Jim

[DCM Fan]

<< primary keys, per se,
have no bearing on whether the recordset can be updated or appended.>>

Technically, you're correct, but one DOES NEED a unique index in order for any
JET query to be updatable through a join.

I'm trying to get the OP to think in terms of Primary Keys, however, which have
the added constraint of being non-nullable, and seemed more-appropriate in the
context of what was posted.

So, to be perfectly accurate (and possibly more confusing), I should have said
this instead: "Make sure each table has a Primary Key--or at least a UNIQUE
INDEX."

Back to you!

I dare you to make some tables without PK (nor UI) and then update one of them
through a join...just from a fundamantal database engine standpoint it SHOULD
NOT BE POSSIBLE. Otherwise, how would Access really know which records to
update? If Access CAN do this, then it must be using its own internal mechanism
for determining 'absolute rows' in the set....I'm not sure if I'm cool with
that.

(Actually, Access MUST use some kind of internal uniqueness mechanism when it's
allowing users to update a table directly, even though the table might not have
a PK nor UI! But I doubt Access can do that through a join...it needs PK or
UI.)

[Jim Allensworth]

On 03 Jul 2004 18:00:14 GMT, dc****@aol.comSPNOAM (DCM Fan) wrote:

<< primary keys, per se,
have no bearing on whether the recordset can be updated or appended.>>

Technically, you're correct, but one DOES NEED a unique index in order for any
JET query to be updatable through a join.

I'm trying to get the OP to think in terms of Primary Keys, however, which have
the added constraint of being non-nullable, and seemed more-appropriate in the
context of what was posted.

So, to be perfectly accurate (and possibly more confusing), I should have said
this instead: "Make sure each table has a Primary Key--or at least a UNIQUE
INDEX."

Back to you!

I'm not interested in a pissing contest and I accept your modified
statements. The thing is; the OP didn't say anything that would
suggest that he was not using PKs. (When creating a table in Access
you must go out of your way to create a table without a PK.) The SQL
he posted was missing - as I noted - the FK from the SELECT statement
which would cause the problem.

If you had responded to him instead of my post then I wouldn't have
posted to you at all.

- Jim

[Martin Lacoste]

dc****@aol.comSPNOAM (DCM Fan) wrote in message news:<20******* *************** *****@mb-m10.aol.com>...

<<What made you come up with those observations/suggestions?>>

10 years of answering questions on this board, obviously posted by novices.

Here are a few more, if those suggestions offended you. Are you joining on the
keyed fields? Are these tables LINKED from a different backend DB than JET? Are
tehre any extraneous properties applied, like filters or sorting?

Thanks for all the responses thus far. And yet, sigh, I'm still no
further than before. There are primary keys present, I have tried
joining on the keyed fields, NOT joining on the keyed fields, etc..
No added filters or sorting present - I purposely made a super simple
query to see if I could indeed eliminate as many variables as
possible, and yet, still, it states it is not updatable.

I am perplexed as to why this is so difficult an issue - between
scouring the help files, searching the internet, including Microsoft's
database, and inquiring on newsgroups, I have yet still not found the
solution to what I thought was a simple problem. That is by no means
intended as an insult to those of you who have provided suggestions -
many thanks! I'm just surprised that either: a) access does not make
some rather simple things actually simple; or.. b) that it is so
difficult for someone with decent computer skills (yes, still learning
access) is having to spend so much time (add to that the time all of
you have taken!) figuring this out.

Well, I'll continue to hunt high and low, and I still invite other
suggestions, no matter how banal - it could be something stupid, but
evidently I'm either not *that* stupid, or much *too* stupid to figure
it out :-(.

Ok, rant over... - thanks again folks!!
Martin Lacoste

[Jim Allensworth]

On 3 Jul 2004 11:56:49 -0700, ma*******@roger s.com (Martin Lacoste)
wrote:

dc****@aol.com SPNOAM (DCM Fan) wrote in message news:<20******* *************** *****@mb-m10.aol.com>...

<<What made you come up with those observations/suggestions?>>

10 years of answering questions on this board, obviously posted by novices.

Here are a few more, if those suggestions offended you. Are you joining on the
keyed fields? Are these tables LINKED from a different backend DB than JET? Are
tehre any extraneous properties applied, like filters or sorting?

Thanks for all the responses thus far. And yet, sigh, I'm still no
further than before. There are primary keys present, I have tried
joining on the keyed fields, NOT joining on the keyed fields, etc..
No added filters or sorting present - I purposely made a super simple
query to see if I could indeed eliminate as many variables as
possible, and yet, still, it states it is not updatable.

I am perplexed as to why this is so difficult an issue - between
scouring the help files, searching the internet, including Microsoft's
database, and inquiring on newsgroups, I have yet still not found the
solution to what I thought was a simple problem. That is by no means
intended as an insult to those of you who have provided suggestions -
many thanks! I'm just surprised that either: a) access does not make
some rather simple things actually simple; or.. b) that it is so
difficult for someone with decent computer skills (yes, still learning
access) is having to spend so much time (add to that the time all of
you have taken!) figuring this out.

Well, I'll continue to hunt high and low, and I still invite other
suggestions, no matter how banal - it could be something stupid, but
evidently I'm either not *that* stupid, or much *too* stupid to figure
it out :-(.

Ok, rant over... - thanks again folks!!
Martin Lacoste

Just curious. Did you SELECT both join keys as I suggested? They must
*both* be in the SELECT statement. They were not in your original
posting you had SELECTED only 1. You could copy and post the SQL of
the query (what you originally posted was obviously not copied)

- Jim