• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 226
  • Last Modified:

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
0
mytfein
Asked:
mytfein
  • 6
  • 5
1 Solution
 
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
The new generation of project management tools

With monday.com’s project management tool, you can see what everyone on your team is working in a single glance. Its intuitive dashboards are customizable, so you can create systems that work for you.

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

Featured Post

Never miss a deadline with monday.com

The revolutionary project management tool is here!   Plan visually with a single glance and make sure your projects get done.

  • 6
  • 5
Tackle projects and never again get stuck behind a technical roadblock.
Join Now