mytfein
asked on
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
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
ASKER
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
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
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
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
ASKER
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
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
ASKER
hi mbizup,
left this idea out.....:
the tables have different keys
and am joining ** instead ** on SSN
tx, s
left this idea out.....:
the tables have different keys
and am joining ** instead ** on SSN
tx, s
Check out my last comment...
ASKER
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
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
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).
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).
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
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
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
ASKER
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
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
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.