Solved

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

Posted on 2012-03-15
11
197 Views
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
ee-Mar-MatchM4-Upload-frontend.mdb
0
Comment
Question by:mytfein
  • 6
  • 5
11 Comments
 
LVL 61

Expert Comment

by:mbizup
ID: 37725078
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
 

Author Comment

by:mytfein
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
0
 
LVL 61

Expert Comment

by:mbizup
ID: 37725396
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
 

Author Comment

by:mytfein
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
ee-Mar-MatchM4-Upload-frontend.mdb
0
 

Author Comment

by:mytfein
ID: 37725423
hi mbizup,

left this idea out.....:

the tables have different keys

    and am joining ** instead ** on SSN

tx, s
0
Complete VMware vSphere® ESX(i) & Hyper-V Backup

Capture your entire system, including the host, with patented disk imaging integrated with VMware VADP / Microsoft VSS and RCT. RTOs is as low as 15 seconds with Acronis Active Restore™. You can enjoy unlimited P2V/V2V migrations from any source (even from a different hypervisor)

 
LVL 61

Expert Comment

by:mbizup
ID: 37725424
Check out my last comment...
0
 

Author Comment

by:mytfein
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

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

Expert Comment

by:mbizup
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).
0
 
LVL 61

Accepted Solution

by:
mbizup earned 500 total points
ID: 37725804
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
 

Author Comment

by:mytfein
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
0
 

Author Comment

by:mytfein
ID: 37726189
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

Top 6 Sources for Identifying Threat Actor TTPs

Understanding your enemy is essential. These six sources will help you identify the most popular threat actor tactics, techniques, and procedures (TTPs).

Join & Write a Comment

Most if not all databases provide tools to filter data; even simple mail-merge programs might offer basic filtering capabilities. This is so important that, although Access has many built-in features to help the user in this task, developers often n…
In Debugging – Part 1, you learned the basics of the debugging process. You learned how to avoid bugs, as well as how to utilize the Immediate window in the debugging process. This article takes things to the next level by showing you how you can us…
As developers, we are not limited to the functions provided by the VBA language. In addition, we can call the functions that are part of the Windows operating system. These functions are part of the Windows API (Application Programming Interface). U…
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…

705 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

Need Help in Real-Time?

Connect with top rated Experts

17 Experts available now in Live!

Get 1:1 Help Now