Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

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

Posted on 2012-03-15
11
Medium Priority
?
224 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
Industry Leaders: 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!

 

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

NEW Veeam Backup for Microsoft Office 365 1.5

With Office 365, it’s your data and your responsibility to protect it. NEW Veeam Backup for Microsoft Office 365 eliminates the risk of losing access to your Office 365 data.

Question has a verified solution.

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

Access developers frequently have requirements to interact with Excel (import from or output to) in their applications.  You might be able to accomplish this with the TransferSpreadsheet and OutputTo methods, but in this series of articles I will di…
Did you know that more than 4 billion data records have been recorded as lost or stolen since 2013? It was a staggering number brought to our attention during last week’s ManageEngine webinar, where attendees received a comprehensive look at the ma…
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…
With Microsoft Access, learn how to specify relationships between tables and set various options on the relationship. Add the tables: Create the relationship: Decide if you’re going to set referential integrity: Decide if you want cascade upda…

972 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