Joined Queries:  does not allow me to modify data in column... is that the way it works?

Posted on 2012-03-15
Last Modified: 2012-06-21
Hi EE,

Does a Joined query allow one to modify data in a column?

        ( the column seems blocked.....)

In other words,
       have a left join that is matching on SSN.

We are joining a vendor file to our table.

If the vendor ssn, is blank, i want to type into the blank column a valid SSN, and
then run the query again, with no rows appearing, as all issues would be resolved.

However, query does let me enter SSN in the column that has nulls

Below is a sample mdb, where the query disallows me from entering data in the the field
that contains NULL.

tx in advance for your help, sandra
Question by:mytfein
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 6
  • 5
LVL 61

Expert Comment

ID: 37725078

The issue is that your JOIN does not involve a Primary Key field.

If you make the Tax ID field in the first table a PK field, the query will be updateable.

Author Comment

ID: 37725338
Hi Mbizup,

tx so much,

i create the tables dynamically, by importing excel files

interestingly, just yesterday, i googled how to create a pk using vba.

i successfully executed the vba to create a pk for one table.

will execute the vba to create pk for the other table and see if the query situation improves.

will get back to you, tx, s
LVL 61

Expert Comment

ID: 37725396

Actually I think that this is not quite going to work for you since the field you are trying to edit is the key field.  

The reason for that is that your query is an "unmatched" query which isolates records in tableA that do not have matching records in tableB.  So the NULL SSN is not actualy related to any record in the second table (it simply indicates a missing match for the record in tableA).

If the vendor ssn, is blank, i want to type into the blank column a valid SSN, and
then run the query again, with no rows appearing, as all issues would be resolved.

If you are simply trying to fill in blank SSN's then instead of a JOIN query, you should use this:

FROM tbl_010_ThisYear_NRMP
Migrating Your Company's PCs

To keep pace with competitors, businesses must keep employees productive, and that means providing them with the latest technology. This document provides the tips and tricks you need to help you migrate an outdated PC fleet to new desktops, laptops, and tablets.


Author Comment

ID: 37725414
hi mbizup,

instead of running vba to create a pk on the second table....

went to the sample mdb above, and manually set the pk of the second table.

1st table key:  LN, FN, AAMID

     created via vba (they are not contiguous fields, in fact the order of the fields
               in table design is:  AAMID, LN, FN)

2nd table key:  ID

then i ran the query, and it still disallows me to enter data in the null field.....

reuploaded mdb with both tables having keys below...

tx, s

Author Comment

ID: 37725423
hi mbizup,

left this idea out.....:

the tables have different keys

    and am joining ** instead ** on SSN

tx, s
LVL 61

Expert Comment

ID: 37725424
Check out my last comment...

Author Comment

ID: 37725613
hi mbizup,

tx... just read your comment.... that makes sense

    the NULL field is a "virtual" field

i understand your suggestion....

- however, as an experiment did the following:

changed the join to join on 2 fields:  LN  and FN
           making it an equi-join


    bec. the vendor file could have an SSN, but it was entered differently then
             what we have on our master table

so now am showing in query:
         LN, FN, fileA_SSN,  fileB_SSN

and they are "real"  fields, but query is not allowing me to modify....

uploaded mdb below with query modifed....

tx, s
LVL 61

Expert Comment

ID: 37725644
That method will work if you want to associate the names...

However as earlier, to make the query updateable you will have to make the joined fields Primary Keys (LastName and FirstName, if set up as PKs will allow you to edit the data).
LVL 61

Accepted Solution

mbizup earned 500 total points
ID: 37725804

btw, your overall goal here is not entirely clear.

Looking at the big pucture, are you simply trying to update the NULL SSN fields with the known SSNs for that person?

If so, you could automate it with an update query (no tweaks to the PK fields needed):

UPDATE tableB, tableA SET tableB.SSN = TableA.SSN
WHERE tableB.SSN IS NULL and TableA.FN = tableB.FirstName AND TableA.LN = tableB.LastName

That's it in essence - you'd have to substitue your actual table and fieldnames, of course.

Author Comment

ID: 37726180
hi mbizup,

yes, that's exactly what i want to do...

i just wanted to give the user a screen to do this manually, so they could think about it
without me doing the update in one swoop....

(i read somewhere that it takes a second to run an update/delete and then days to restore
if did not save a backup, and in this case wanted the user to do it manually by opening a query screen)

i did not realize that i could do it thru code

i do have a question about the UPDATE statement, please:

UPDATE tableB, tableA

you can mention more than 1 table in an update statement
       is that possible?

the UPDATE idea is very useful..... tx so much....

still am puzzled as why the mdb that i updated with joins on name, with real columns
still disallows me from modifying....  

tx again, s

Author Comment

ID: 37726189
hi mbizup,

oh sorry... missed your previous remark that you further explain about primary keys
and joins and queries being updateable....


<That method will work if you want to associate the names...

However as earlier, to make the query updateable you will have to make the joined fields Primary Keys (LastName and FirstName, if set up as PKs will allow you to edit the data).>

so this post is resolved, tx again for your help, sandra

Featured Post

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
Email question 19 87
Outlook VBA question - How do I mark a message as read 2 55
How to get data off Lotus Notes server 4 62
Using a combo box to search a form. 3 30
In a multiple monitor setup, if you don't want to use AutoCenter to position your popup forms, you have a problem: where will they appear?  Sometimes you may have an additional problem: where the devil did they go?  If you last had a popup form open…
It’s been over a month into 2017, and there is already a sophisticated Gmail phishing email making it rounds. New techniques and tactics, have given hackers a way to authentically impersonate your contacts.How it Works The attack works by targeti…
Get people started with the utilization of class modules. Class modules can be a powerful tool in Microsoft Access. They allow you to create self-contained objects that encapsulate functionality. They can easily hide the complexity of a process from…
Using Microsoft Access, learn some simple rules for how to construct tables in a relational database. Split up all multi-value fields into single values: Split up fields that belong to other things into separate tables: Make sure that all record…

737 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question