Cannot ammend JOIN query (1 Viewer)

Gavx · Dec 15, 2019

I am using a query to compare 2 tables - lets say tblNewTransactions and tblTransactions where I am looking for records in the former that are not in the latter (because some definitely are).

The outputted records have a checkbox field. The idea was to be able select each of the checkboxes of the records outputted so I could then return to the tblNewTransactions and review the records whose checkboxes have been selected and then once satisfied import them into tblTransactions.

Problem is I cannot amend the results of the query. After doing a bit of research I discovered you cannot amend the results of a JOIN query and figured this is my problem.

Is there a way of achieving my objective of wanting to review the records not in tblTransactions. Am I going about this the wrong way?

Any help would be great.

jdraw · Dec 15, 2019

Gavx,

I recommend you post your table designs, the query involved and some sample records or a copy of your database
Normally, the approach would be to use the Find Unmatched Query option in the query wizard.
See if any of the links in Similar Threads at the bottom of this thread apply.

Good luck with your project.

isladogs · Dec 15, 2019

Gavx said:
I am using a query to compare 2 tables - lets say tblNewTransactions and tblTransactions where I am looking for records in the former that are not in the latter (because some definitely are). .

As jdraw has already indicated, use an unmatched query - there is a query wizard to help you do this

Problem is I cannot amend the results of the query. After doing a bit of research I discovered you cannot amend the results of a JOIN query and figured this is my problem.

There are lots of reasons why queries can become read only. See http://allenbrowne.com/ser-61.html

However, if none of those apply, a query based on 2 joined tables can be edited

The_Doc_Man · Dec 15, 2019

If the checkboxes are in the table being reviewed for discrepancies and there are adequate key fields to allow it, you could also do a subquery, something like:

Code:

UPDATE tblNewTransactions SET CkBox = FALSE ;
UPDATE tblNewTransactions SET CkBox = TRUE WHERE
tblNewTransactions.Key NOT IN ( SELECT tblTransactions.Key FROM tblTransactions ) ;

Admittedly, if your selection criteria become too complex, a subquery might not be your best choice, but this doesn't involve a JOIN. Still, you might be able to look at this as a way to handle the problem.

isladogs · Dec 15, 2019

Whilst the Doc's idea will work, using an unmatched query & then modifying it to an UPDATE query should not be read only
The query SQL will be something like this:

Code:

UPDATE tblNewTransactions LEFT JOIN tblTransactions ON tblNewTransactions.[IDField] = tblTransactions.[IDField]
SET tblNewTransactions.YesNoField = True
WHERE (((tblTransactions.IDField) Is Null));

Obviously you need to use your own field names in place of IDField & YesNoField

The_Doc_Man · Dec 15, 2019

Colin, I'm actually with you on that, but the claim of a query that can't be amended to become an UPDATE gave me pause. I was offering an alternative, admittedly of lower quality than the idea of a "Find Unmatched" query. Guess I didn't make that intent clear.

Gavx · Dec 16, 2019

jdraw said:
Gavx,

...Normally, the approach would be to use the Find Unmatched Query option in the query wizard.

As I recall I originally wrote the query using this wizard.

isladogs said:
Whilst the Doc's idea will work, ...The query SQL will be something like this:

Code:

UPDATE tblNewTransactions LEFT JOIN tblTransactions ON tblNewTransactions.[IDField] = tblTransactions.[IDField] SET tblNewTransactions.YesNoField = True WHERE (((tblTransactions.IDField) Is Null));

I was thinking along these lines but had no idea of how to go about it. I can work with this.
Thanks for your help. Bit snowed under at the moment but will get back to it shortly and post results.
thanks again.

Gavx · Dec 20, 2019

Thank you all, the Update query did exactly what was required. Access is amazing!

Cannot ammend JOIN query (1 Viewer)

Gavx

Registered User.

jdraw

Super Moderator

isladogs

MVP / VIP

The_Doc_Man

Immoderate Moderator

isladogs

MVP / VIP

The_Doc_Man

Immoderate Moderator

Gavx

Registered User.

Gavx

Registered User.

Similar threads

Users who are viewing this thread