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.Numbe r,
LastName.ID, LastName.Number
FROM LastName INNER JOIN FirstName ON (LastName.Numbe r =
FirstName.Numbe r) 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 4 6762
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****@program consultants.com > wrote in message
news:11******** **************@ g44g2000cwa.goo glegroups.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.Numbe r, LastName.ID, LastName.Number FROM LastName INNER JOIN FirstName ON (LastName.Numbe r = FirstName.Numbe r) 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
"Larry Linson" <bo*****@localh ost.not> wrote in
news:mRrse.4027 $kj5.950@trnddc 03: 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
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 ***
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 This thread has been closed and replies have been disabled. Please start a new discussion. Similar topics |
by: aaron |
last post by:
I have a question about (i think) joining.
If I have a table in a database that has this info:
key - name - favorite
1 - john - 2
2 - judy - 3
3 - joe - 1
the favorite icecream table is this:
|
by: Mario Zoratti |
last post by:
Hello, everybody,
Please be so kind to explain how it is possible to make a query pass
through in Access 97 vs SQL server updatable. The query is "SELECT
CDC. * FROM CDC": it is very simple and there is no join with other
tables.
The same table linked directly to SQL server is updatable, while the
query isn't.
Thanks very much
Mario.
|
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 table, the resulting query is
not updatable. OR If I link 2 tables together that don't have a primary key
the result is not updatable.
I know I was able to do this in previous versions since I usually don't use
a primary key.
Thanks
|
by: Martin Lacoste |
last post by:
I have a very basic query that Access 2000 will not allow to be
updatable. I've gone through all the situations in the help file
where data cannot be updated, and I don't see that this query belongs
to any of those. I have checked all permissions, locks, etc.. - can't
find anything out of the ordinary that would explain it. Deleted the
DistinctRow - no change. The SQL version is below - anyone notice the
problem or have any...
|
by: Martin Lacoste |
last post by:
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;
| |
by: Enterprise |
last post by:
Hi,
Here is my code for the Union query "Table
union Table union Table ;"
The tables are actually queries themselves.
I made a form for the query, but when I try to update the form, I get a
message that says "This recordset is not updatable." Can I somehow make it
updatable?
Thanks
P.S. Using access 2000.
|
by: CC |
last post by:
Dear all,
Following Many-to-Many table structure is part of my database
-----------------------------------
TbGeneral (dossier information)
PK: dsr_id
TbDossierSample
PK: dsr_sample_id
|
by: sparks |
last post by:
I have read in a bunch of files and saved the filename in the table
along with the idnumbers and names.
Thing is they sent us some files that have to be changed now
figures LOL
in the table I have filename field and name field
no problem with an update if filename = "ABC.F" name ="BOB"
but I have 30 of these.
|
by: geolemon |
last post by:
I'm trying to perform an update, and I can't avoid this error - I've tried this query what must be 5 different very fundamentally different ways now.
Arg.
I used to be a DBA in large DB2 and SQL Server environments, but I'm a little rusty, plus I'm very new to Access.
I am trying to perform a calculation based on information in a few tables, and use the result to update another.
Should be simple, here's what counts from the tables...
|
by: marktang |
last post by:
ONU (Optical Network Unit) is one of the key components for providing high-speed Internet services. Its primary function is to act as an endpoint device located at the user's premises. However, people are often confused as to whether an ONU can Work As a Router. In this blog post, we’ll explore What is ONU, What Is Router, ONU & Router’s main usage, and What is the difference between ONU and Router. Let’s take a closer look !
Part I. Meaning of...
|
by: Oralloy |
last post by:
Hello folks,
I am unable to find appropriate documentation on the type promotion of bit-fields when using the generalised comparison operator "<=>".
The problem is that using the GNU compilers, it seems that the internal comparison operator "<=>" tries to promote arguments from unsigned to signed.
This is as boiled down as I can make it.
Here is my compilation command:
g++-12 -std=c++20 -Wnarrowing bit_field.cpp
Here is the code in...
| |
by: jinu1996 |
last post by:
In today's digital age, having a compelling online presence is paramount for businesses aiming to thrive in a competitive landscape. At the heart of this digital strategy lies an intricately woven tapestry of website design and digital marketing. It's not merely about having a website; it's about crafting an immersive digital experience that captivates audiences and drives business growth.
The Art of Business Website Design
Your website is...
|
by: Hystou |
last post by:
Overview:
Windows 11 and 10 have less user interface control over operating system update behaviour than previous versions of Windows. In Windows 11 and 10, there is no way to turn off the Windows Update option using the Control Panel or Settings app; it automatically checks for updates and installs any it finds, whether you like it or not. For most users, this new feature is actually very convenient. If you want to control the update process,...
|
by: isladogs |
last post by:
The next Access Europe User Group meeting will be on Wednesday 1 May 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 a new presenter, Adolph Dupré who will be discussing some powerful techniques for using class modules.
He will explain when you may want to use classes instead of User Defined Types (UDT). For example, to manage the data in unbound forms.
Adolph will...
|
by: TSSRALBI |
last post by:
Hello
I'm a network technician in training and I need your help.
I am currently learning how to create and manage the different types of VPNs and I have a question about LAN-to-LAN VPNs.
The last exercise I practiced was to create a LAN-to-LAN VPN between two Pfsense firewalls, by using IPSEC protocols.
I succeeded, with both firewalls in the same network. But I'm wondering if it's possible to do the same thing, with 2 Pfsense firewalls...
|
by: adsilva |
last post by:
A Windows Forms form does not have the event Unload, like VB6. What one acts like?
|
by: 6302768590 |
last post by:
Hai team
i want code for transfer the data from one system to another through IP address by using C# our system has to for every 5mins then we have to update the data what the data is updated we have to send another system
| |
by: muto222 |
last post by:
How can i add a mobile payment intergratation into php mysql website.
| |