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

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
ee-Mar-MatchM4-Upload-frontend.mdb
mytfeinAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

mbizupCommented:
Sandra,

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.
0
mytfeinAuthor Commented:
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
0
mbizupCommented:
Sandra,

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:

SELECT *
FROM tbl_010_ThisYear_NRMP
WHERE SSN IS NULL
0
IT Pros Agree: AI and Machine Learning Key

We’d all like to think our company’s data is well protected, but when you ask IT professionals they admit the data probably is not as safe as it could be.

mytfeinAuthor Commented:
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
ee-Mar-MatchM4-Upload-frontend.mdb
0
mytfeinAuthor Commented:
hi mbizup,

left this idea out.....:

the tables have different keys

    and am joining ** instead ** on SSN

tx, s
0
mbizupCommented:
Check out my last comment...
0
mytfeinAuthor Commented:
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

why?

    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
ee-Mar-MatchM4-Upload-frontend.mdb
0
mbizupCommented:
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).
0
mbizupCommented:
Sandra,

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.
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
mytfeinAuthor Commented:
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
0
mytfeinAuthor Commented:
hi mbizup,

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

here:

<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
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.