473,326 Members | 2,148 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,326 software developers and data experts.

Are select queries with inner joins updatable?

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
Nov 13 '05 #1
14 2517
On 2 Jul 2004 14:41:30 -0700, ma*******@rogers.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
Nov 13 '05 #2
Two things: 1) Make sure the tables have primary keys, and 2) make sure you
have the latest JET update.
Nov 13 '05 #3
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?

Nov 13 '05 #4
<<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?
Nov 13 '05 #5
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

Nov 13 '05 #6
<< 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.)
Nov 13 '05 #7
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
Nov 13 '05 #8
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
Nov 13 '05 #9
On 3 Jul 2004 11:56:49 -0700, ma*******@rogers.com (Martin Lacoste)
wrote:
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


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

Post your table structure including relationships.
There's no obvious reason from the information you have
given why your original query isn't updateable as you posted
it.

"Martin Lacoste" <ma*******@rogers.com> wrote in message
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

Nov 13 '05 #11
<<The SQL
he posted was missing - as I noted - the FK from the SELECT statement
which would cause the problem. >>

And my problem with your post is that THIS INFO is not accurate...and my
original suggestion still holds....and I claim the OP is leaving something out
of the info he's giving us (or he hasn't updated JET yet)

HOWEVER, if adding the FK *DOES* cause a certain statement that is not
updatable to become updatable, then I say there is a JET problem!

It's trivial to test everying we've both stated. You'll see that you're wrong
on your assertion that you need to have EITHER key in the SELECT, let alone the
FK. I would think you've already done this, but then again, you would've seen
your fallacy...

I'm not into pissing contests, either, but I *am* into delivering accurate
information, and I *am* into exposing misinformation.
Nov 13 '05 #12
Martin,
Generally speaking, as long as the query has the primary key column from the
one-side of a one-to-many relationship it should be updatable.

"Martin Lacoste" <ma*******@rogers.com> wrote in message
news:d4**************************@posting.google.c om...
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

Nov 13 '05 #13
Try adding the word DISTINCTROW after the SELECT keyword.
--
MichKa [MS]
NLS Collation/Locale/Keyboard Development
Globalization Infrastructure and Font Technologies
Windows International Division

This posting is provided "AS IS" with
no warranties, and confers no rights.
"Martin Lacoste" <ma*******@rogers.com> wrote in message
news:d4**************************@posting.google.c om...
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

Nov 13 '05 #14
To Jim (and Mike, and rck, and Alan, etc..!)

Thanks very much for all your replies - looks like I started a minor
flame war to boot - ah, life is never boring!!

Anyways - believe it or not, give or take, all of the responses were
helpful to varying degrees, in letting me think through the process.
Jim, I must have misunderstood you initially - I probably thought all
I had to do was *include* the primary keys in the query, not actually
make them a part of the join. Once I did that, then I was home free -
so, many thanks!!

I am disappointed that this is not made clear in the Access help (are
you listening Microsoft?), since it took me much too long to figure
this out. But, I thank all of you for your time and advice.

Thanks!
Martin Lacoste

Ji****@NOTdatacentricsolutions.com (Jim Allensworth) wrote in message news:<40***************@netnews.comcast.net>...
On 3 Jul 2004 11:56:49 -0700, ma*******@rogers.com (Martin Lacoste)
wrote:
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


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

Nov 13 '05 #15

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

2
by: Bernd Kohler | last post by:
Hello, some little questions relating LAMP (PHP 4.3.4, MySQL 3.x, Apache): I'm trying to build a community website which needs many db-querys for lots of concurrent users. Therefore I'm always...
4
by: Denis St-Michel | last post by:
Hello All, Hope some Guru will be able to help me with this. Let's take this example table A ------------------------------------------------------------------------------- id | ...
1
by: Gerald Maher | last post by:
Hi I need to select 2 tables . The field 'p_description' is not always aviable but i still need to print it to the screen SELECT * FROM task,p_description WHERE ComponentIDLink = 34 OR...
23
by: Brian | last post by:
Hello All - I am wondering if anyone has any thoughts on which is better from a performance perspective: a nested Select statement or an Inner Join. For example, I could do either of the...
2
by: Johnny M | last post by:
I have been using Access since office 4.3. I have just upgraded to Office 2003 and am having issues with creating updatable queries. If I link one table to a selection query based on the same...
6
by: Carlo B | last post by:
Hi I need to use a listbox to display authors from the authors table in the standard BIBLIO.MDB. I haven't even got to this point yet and the code below is bombing out on the line...
2
by: Pasquale | last post by:
I am using the query below for a multi event registration confirmation page. Any one of these events can have individuals and/or teams, which also then can have different categories of individuals...
6
by: dmonroe | last post by:
hi group -- Im having a nested inner join problem with an Access SQl statement/Query design. Im running the query from ASP and not usng the access interface at all. Here's the tables: ...
22
by: MP | last post by:
vb6,ado,mdb,win2k i pass the sql string to the .Execute method on the open connection to Table_Name(const) db table fwiw (the connection opened via class wrapper:) msConnString = "Data Source="...
0
by: DolphinDB | last post by:
Tired of spending countless mintues downsampling your data? Look no further! In this article, you’ll learn how to efficiently downsample 6.48 billion high-frequency records to 61 million...
0
by: ryjfgjl | last post by:
ExcelToDatabase: batch import excel into database automatically...
1
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
0
by: jfyes | last post by:
As a hardware engineer, after seeing that CEIWEI recently released a new tool for Modbus RTU Over TCP/UDP filtering and monitoring, I actively went to its official website to take a look. It turned...
0
by: ArrayDB | last post by:
The error message I've encountered is; ERROR:root:Error generating model response: exception: access violation writing 0x0000000000005140, which seems to be indicative of an access violation...
1
by: Defcon1945 | last post by:
I'm trying to learn Python using Pycharm but import shutil doesn't work
1
by: Shællîpôpï 09 | last post by:
If u are using a keypad phone, how do u turn on JavaScript, to access features like WhatsApp, Facebook, Instagram....
0
by: af34tf | last post by:
Hi Guys, I have a domain whose name is BytesLimited.com, and I want to sell it. Does anyone know about platforms that allow me to list my domain in auction for free. Thank you
0
isladogs
by: isladogs | last post by:
The next Access Europe User Group meeting will be on Wednesday 3 Apr 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM). In this session, we are pleased to welcome former...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.