How to ignore fields in Update Query

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

Hi everyone

I have two tables. All incoming data goes into Table A. Table B are certain records from Table A. What happens is that some data goes into Table A that really should be updated to Table B. It ends up that, for a particular record (row), some fields are blank and some are filled in Table A, and different fields in Table B are alternately filled/blank.
So I need to update Table B's blank fields with Table A's non-blank fields. The thing is that I don't want to overwrite any data that's already in Table B. It seems that the only query I can make so far will overwrite all of Table B's data, blank fields AND filled ones. How do I express the criteria of wanting only to update a field if there's no data already in it?

Thanks in advance for your help!
 
So I need to update Table B's blank fields with Table A's non-blank fields. The thing is that I don't want to overwrite any data that's already in Table B. It seems that the only query I can make so far will overwrite all of Table B's data, blank fields AND filled ones. How do I express the criteria of wanting only to update a field if there's no data already in it?
Click to expand...

You can update TableB.Fieldname to

NZ([TableB].[Fieldname], [TableA].[Fieldname])

If it's NULL, it will use the value from tableA; if it isn't NULL, it
will update to its currently existing value.
 
Thanks for the suggestion. I added that expression, and previewed my results. I have to admit I'm confused. What my query is doing now is that it's only "updating" the records in Table B that correspond to blank records in Table A. The NZ function appears to be working -- the query is choosing the filled fields of Table B over the blank ones in Table A -- but it looks like the query is only choosing to look at records in Table B which correspond to completely blank ones in Table A.
What I had hoped to do is make Access look at each field in each record in Table B, and look at the corresponding fields in Table A --- then update Table B with any new information that Table A might have.

Perhaps there is something I'm missing here?

Any help would be appreciated!


John Vinson said:
So I need to update Table B's blank fields with Table A's non-blank fields. The thing is that I don't want to overwrite any data that's already in Table B. It seems that the only query I can make so far will overwrite all of Table B's data, blank fields AND filled ones. How do I express the criteria of wanting only to update a field if there's no data already in it?
Click to expand...

You can update TableB.Fieldname to

NZ([TableB].[Fieldname], [TableA].[Fieldname])

If it's NULL, it will use the value from tableA; if it isn't NULL, it
will update to its currently existing value.
Click to expand...
 
Thanks for the suggestion. I added that expression, and previewed my results. I have to admit I'm confused. What my query is doing now is that it's only "updating" the records in Table B that correspond to blank records in Table A. The NZ function appears to be working -- the query is choosing the filled fields of Table B over the blank ones in Table A -- but it looks like the query is only choosing to look at records in Table B which correspond to completely blank ones in Table A.
What I had hoped to do is make Access look at each field in each record in Table B, and look at the corresponding fields in Table A --- then update Table B with any new information that Table A might have.

Perhaps there is something I'm missing here?
Click to expand...

You'll need that expression under EACH FIELD that you want to update -
it sounds like you're only updating one.

Please post the SQL view of your query - I'm not sure just what you're
trying!
 
Here is my SQL view of my query:

UPDATE TableA INNER JOIN TableB ON TableA.Number = TableB.Number SET TableA.Number = TableB.Number, TableA.Name
= TableB.Name, TableA.Description = TableB.Description, TableA.Job = TableB.Job, TableA.CodeA = TableB.CodeA,
TableA.CodeB = TableB.CodeB, TableA.CodeC = TableB.CodeC
WHERE (((TableA.Name)=NZ([TableA].[Name],[TableB].[Name])) AND
((TableA.Description)=NZ([TableA].[Description],[TableB].[Description])) AND
((TableA.Job)=NZ([TableA].[Job],[TableB].[Job])) AND ((TableA.CodeA)=NZ([TableA].[CodeA],[TableB].[CodeA])) AND
((TableA.[CodeB])=NZ([TableA].[CodeB],[TableB].[CodeB])) AND
((TableA.CodeC)=NZ([TableA].[CodeC],[TableB].[CodeC])));

Did I set up my query wrong? Like I mentioned before, I just want Access to look at two tables, and update one table with any new information (not the blank fields) from the other table.
 
Pardon me, but what Mr. Vinson said was

UPDATE TableA INNER JOIN TableB ON TableA.Number = TableB.Number
SET
TableA.[Name] = NZ(TableA.[Name],TableB.[Name]),
TableA.Description=NZ([TableA].[Description],[TableB].[Description]),
....

NO WHERE CLAUSE needed. Although you could test each field to see if it was null.

WHERE TableA.[Name] is Null OR TableA.Description is Null or ...

With the join on TableA.Number to TableB.Number there is never going to be a
case where a record is returned and the Numbers are null; so don't bother to
update that field or check that field for null.

Also, you have a potential for problems to develop with fieldnames like Name,
Description. EVERY object in Access has a NAME property and many have the
option of having a DESCRIPTION property.
Here is my SQL view of my query:

UPDATE TableA INNER JOIN TableB ON TableA.Number = TableB.Number SET TableA.Number = TableB.Number, TableA.Name
= TableB.Name, TableA.Description = TableB.Description, TableA.Job = TableB.Job, TableA.CodeA = TableB.CodeA,
TableA.CodeB = TableB.CodeB, TableA.CodeC = TableB.CodeC
WHERE (((TableA.Name)=NZ([TableA].[Name],[TableB].[Name])) AND
((TableA.Description)=NZ([TableA].[Description],[TableB].[Description])) AND
((TableA.Job)=NZ([TableA].[Job],[TableB].[Job])) AND ((TableA.CodeA)=NZ([TableA].[CodeA],[TableB].[CodeA])) AND
((TableA.[CodeB])=NZ([TableA].[CodeB],[TableB].[CodeB])) AND
((TableA.CodeC)=NZ([TableA].[CodeC],[TableB].[CodeC])));

Did I set up my query wrong? Like I mentioned before, I just want Access to look at two tables, and update one table with any new information (not the blank fields) from the other table.

John Vinson said:
You'll need that expression under EACH FIELD that you want to update -
it sounds like you're only updating one.

Please post the SQL view of your query - I'm not sure just what you're
trying!
Click to expand...
Click to expand...
 
Here is my SQL view of my query:

UPDATE TableA INNER JOIN TableB ON TableA.Number = TableB.Number SET TableA.Number = TableB.Number, TableA.Name
= TableB.Name, TableA.Description = TableB.Description, TableA.Job = TableB.Job, TableA.CodeA = TableB.CodeA,
TableA.CodeB = TableB.CodeB, TableA.CodeC = TableB.CodeC
WHERE (((TableA.Name)=NZ([TableA].[Name],[TableB].[Name])) AND
((TableA.Description)=NZ([TableA].[Description],[TableB].[Description])) AND
((TableA.Job)=NZ([TableA].[Job],[TableB].[Job])) AND ((TableA.CodeA)=NZ([TableA].[CodeA],[TableB].[CodeA])) AND
((TableA.[CodeB])=NZ([TableA].[CodeB],[TableB].[CodeB])) AND
((TableA.CodeC)=NZ([TableA].[CodeC],[TableB].[CodeC])));

Did I set up my query wrong?
Click to expand...

Yes. You used the NZ expession on the *criteria* line; I intended (but
probably didn't clearly say) it should be the *update to* line.
Like I mentioned before, I just want Access to look at two tables, and update one table with any new information (not the blank fields) from the other table.
Click to expand...

Try

UPDATE TableA INNER JOIN TableB ON TableA.Number = TableB.Number
SET TableA.[Name] = NZ([TableA].[Name],[TableB].[Name]),
TableA.Description =
NZ([TableA].[Description],[TableB].[Description]),
TableA.Job = NZ([TableA].[Job],[TableB].[Job]),
TableA.CodeA = NZ([TableA].[CodeA],[TableB].[CodeA]),
TableA.CodeB = NZ([TableA].[CodeB],[TableB].[CodeB]),
TableA.CodeC = NZ([TableA].[CodeC],[TableB].[CodeC])
WHERE TableA.[Name])<>TableB.[Name]
OR TableA.[Description] <> TableB.[Description]
OR TableA.Job <> TableB.Job
OR TableA.CodeA <> TableB.CodeA
OR TableA.CodeB <> TableB.CodeB
OR TableA.CodeC <> TableB.CodeC;

The WHERE clause will limit retrieval to those records where there is
data in both tables but it doesn't match, or where one table has a
NULL and the other has data; the SET clause will leave the value alone
if TableB has a NULL but will replace the data if TableB has any data
at all.
 
Back
Top