How to allow edits to a query (1 Viewer)

TobyMace

Registered User.
Local time
Today, 14:42
Joined
Apr 13, 2018
Messages
65
Hi all,

I have a query that gets info from 3 tables. (See attached)
However using the links that I have used and require It does not allow edits or additions. I understand why it is not letting me do this but I am asking for the best work around. I would only need to make edits and not necessarily additions although I imagine the two come hand in hand.
Many thanks in advance!
 

Attachments

  • Untitled.png
    Untitled.png
    97.3 KB · Views: 57

theDBguy

I’m here to help
Staff member
Local time
Today, 07:42
Joined
Oct 29, 2018
Messages
21,358
Hi. One workaround is to use a temporary table. For instance, you could change your SELECT query into a MAKE TABLE query and then use the temporary table to make your changes. However, it means you'll have to apply those changes to the actual tables as well.
 

isladogs

MVP / VIP
Local time
Today, 14:42
Joined
Jan 14, 2017
Messages
18,186
Do you need two outer joins?
You could also try changing the query to SELECT DISTINCTROW (unique records =yes) and/or join two tables in query A then join that query to table 3.
It may not help but worth a try.
 

TobyMace

Registered User.
Local time
Today, 14:42
Joined
Apr 13, 2018
Messages
65
Hi Both,

Thank you for your replies and I apologise for the delay in getting back to you. I have found a solution by the user being able to switch between an "edit" mode and "search" mode on a form. So at the click of a button they can record straight to the table or view info. I would like to try your methods though when I get a chance though!

Many thanks again.
 

Micron

AWF VIP
Local time
Today, 10:42
Joined
Oct 20, 2018
Messages
3,476
AFAIK, a stacked query that has one or more queries that are not editable will not be editable either, and the DISTINCT predicate makes a query not editable. Would be interesting if it turned out to not be so.
 

Pat Hartman

Super Moderator
Staff member
Local time
Today, 10:42
Joined
Feb 19, 2002
Messages
42,970
The problem is that the query is not joining tables on PK to FK. It is joining them on data field to FK. If there were only two tables in the query, Access might be able to make the query updateable but with three tables, you are getting a Cartesian Product.

@Micron,
Stacked queries ARE updateable. There are lots of things that make queries not updateable but nesting them isn't one of the problems.
 

Micron

AWF VIP
Local time
Today, 10:42
Joined
Oct 20, 2018
Messages
3,476
@Micron,
Stacked queries ARE updateable. There are lots of things that make queries not updateable but nesting them isn't one of the problems.
I didn't say they aren't. I said IF an underlying query in the stack is NOT updatable.
 

Users who are viewing this thread

Top Bottom