editing records in a query that draws from 2 tables

[Guest]

I have queries set up in two different databases that are supposed to work
exactly the same: each query draws from two tables within the database, and I
need to be able to edit records while I'm in the query. I've been told that
it's impossible to edit records in a query that draws from two tables, but
the odd thing is that in one database where I have the query set up it works,
but in the other one it doesn't.

I'm not knowledgeable about Access, but I've looked in all the properties I
could find to see if the queries are set up the same way (looking for a
reason why it would work in one database but not the other). I can't find
any differences that would explain it.

Here is the SQL stuff from Database 1, where the query DOES work (i.e. I am
able to edit records within the query, even though the query draws from two
tables):

SELECT [Main table].p1title, [Main table].p1first, [Main table].p1last,
[Main table].address, [Main table].city, [Main table].state, [Main
table].zip, [Part 2 main table].PQ3, [Part 2 main table].[Parent paid for PQ3]
FROM [Main table] INNER JOIN [Part 2 main table] ON [Main table].StudyID =
[Part 2 main table].StudyID
WHERE ((([Part 2 main table].PQ3)=Yes) AND (([Part 2 main table].[Parent
paid for PQ3])=No));


Here is the SQL stuff from Database 2, where the query DOES NOT work (i.e. I
am NOT able to edit records within the query, which draws from two tables):

SELECT [Main Table].p1title, [Main Table].p1first, [Main Table].p1last,
[Part 2 main table].PQ2, [Part 2 main table].[PQ2 Paid], [Main
Table].[address-current], [Main Table].[city-current], [Main
Table].[state-current], [Main Table].[zip-current]
FROM [Main Table] INNER JOIN [Part 2 main table] ON [Main Table].StudyID =
[Part 2 main table].StudyID
WHERE ((([Part 2 main table].PQ2)=Yes) AND (([Part 2 main table].[PQ2
Paid])=No));


In case it helps, the field that I need to be able to edit within the query
is "Parent paid for PQ3" (in Database 1) and "PQ2 Paid" (in Database 2). I
just need to be able to change the "No"s to "Yes"s (something that I can do
in Database 1, but can't do in Database 2).

Any thoughts on why this might work in one database but not the other (even
though they both seem to be set up the same way)? Thanks!

 

[Roger Carlson]

You CAN edit a query that is composed of a Join of two tables IF the join
field on the "One" side of a One-to-Many relationship is either a Primary
Key or has a Unique Index (NoDuplicates) on it. My guess is that in the
first query, StudyID is the primary key of [Main table]. Make sure StudyID
is the primary key (or has a UI) of [Main Table] in the other database as
well.

--
--Roger Carlson
Access Database Samples: www.rogersaccesslibrary.com
Want answers to your Access questions in your Email?
Free subscription:
http://peach.ease.lsoft.com/scripts/wa.exe?SUBED1=ACCESS-L

I have queries set up in two different databases that are supposed to work
exactly the same: each query draws from two tables within the database, and I
need to be able to edit records while I'm in the query. I've been told that
it's impossible to edit records in a query that draws from two tables, but
the odd thing is that in one database where I have the query set up it works,
but in the other one it doesn't.

I'm not knowledgeable about Access, but I've looked in all the properties I
could find to see if the queries are set up the same way (looking for a
reason why it would work in one database but not the other). I can't find
any differences that would explain it.

Here is the SQL stuff from Database 1, where the query DOES work (i.e. I am
able to edit records within the query, even though the query draws from two
tables):

SELECT [Main table].p1title, [Main table].p1first, [Main table].p1last,
[Main table].address, [Main table].city, [Main table].state, [Main
table].zip, [Part 2 main table].PQ3, [Part 2 main table].[Parent paid for PQ3]
FROM [Main table] INNER JOIN [Part 2 main table] ON [Main table].StudyID =
[Part 2 main table].StudyID
WHERE ((([Part 2 main table].PQ3)=Yes) AND (([Part 2 main table].[Parent
paid for PQ3])=No));


Here is the SQL stuff from Database 2, where the query DOES NOT work (i.e. I
am NOT able to edit records within the query, which draws from two tables):

SELECT [Main Table].p1title, [Main Table].p1first, [Main Table].p1last,
[Part 2 main table].PQ2, [Part 2 main table].[PQ2 Paid], [Main
Table].[address-current], [Main Table].[city-current], [Main
Table].[state-current], [Main Table].[zip-current]
FROM [Main Table] INNER JOIN [Part 2 main table] ON [Main Table].StudyID =
[Part 2 main table].StudyID
WHERE ((([Part 2 main table].PQ2)=Yes) AND (([Part 2 main table].[PQ2
Paid])=No));


In case it helps, the field that I need to be able to edit within the query
is "Parent paid for PQ3" (in Database 1) and "PQ2 Paid" (in Database 2). I
just need to be able to change the "No"s to "Yes"s (something that I can do
in Database 1, but can't do in Database 2).

Any thoughts on why this might work in one database but not the other (even
though they both seem to be set up the same way)? Thanks!

 

[Guest]

That worked! Thank you so much for your help!

You CAN edit a query that is composed of a Join of two tables IF the join
field on the "One" side of a One-to-Many relationship is either a Primary
Key or has a Unique Index (NoDuplicates) on it. My guess is that in the
first query, StudyID is the primary key of [Main table]. Make sure StudyID
is the primary key (or has a UI) of [Main Table] in the other database as
well.

--
--Roger Carlson
Access Database Samples: www.rogersaccesslibrary.com
Want answers to your Access questions in your Email?
Free subscription:
http://peach.ease.lsoft.com/scripts/wa.exe?SUBED1=ACCESS-L

I have queries set up in two different databases that are supposed to work
exactly the same: each query draws from two tables within the database, and I
need to be able to edit records while I'm in the query. I've been told that
it's impossible to edit records in a query that draws from two tables, but
the odd thing is that in one database where I have the query set up it works,
but in the other one it doesn't.

I'm not knowledgeable about Access, but I've looked in all the properties I
could find to see if the queries are set up the same way (looking for a
reason why it would work in one database but not the other). I can't find
any differences that would explain it.

Here is the SQL stuff from Database 1, where the query DOES work (i.e. I am
able to edit records within the query, even though the query draws from two
tables):

SELECT [Main table].p1title, [Main table].p1first, [Main table].p1last,
[Main table].address, [Main table].city, [Main table].state, [Main
table].zip, [Part 2 main table].PQ3, [Part 2 main table].[Parent paid for PQ3]
FROM [Main table] INNER JOIN [Part 2 main table] ON [Main table].StudyID =
[Part 2 main table].StudyID
WHERE ((([Part 2 main table].PQ3)=Yes) AND (([Part 2 main table].[Parent
paid for PQ3])=No));


Here is the SQL stuff from Database 2, where the query DOES NOT work (i.e. I
am NOT able to edit records within the query, which draws from two tables):

SELECT [Main Table].p1title, [Main Table].p1first, [Main Table].p1last,
[Part 2 main table].PQ2, [Part 2 main table].[PQ2 Paid], [Main
Table].[address-current], [Main Table].[city-current], [Main
Table].[state-current], [Main Table].[zip-current]
FROM [Main Table] INNER JOIN [Part 2 main table] ON [Main Table].StudyID =
[Part 2 main table].StudyID
WHERE ((([Part 2 main table].PQ2)=Yes) AND (([Part 2 main table].[PQ2
Paid])=No));


In case it helps, the field that I need to be able to edit within the query
is "Parent paid for PQ3" (in Database 1) and "PQ2 Paid" (in Database 2). I
just need to be able to change the "No"s to "Yes"s (something that I can do
in Database 1, but can't do in Database 2).

Any thoughts on why this might work in one database but not the other (even
though they both seem to be set up the same way)? Thanks!

 

[peregenem]

You CAN edit a query that is composed of a Join of two tables IF the join
field on the "One" side of a One-to-Many relationship is either a Primary
Key or has a Unique Index (NoDuplicates) on it.

I'd add, "... and there is sufficient key column information for
updating" to those prerequisites (think OUTER JOIN).