UnseenSpecter UnseenSpecter - 1 year ago 76
SQL Question

In Access 2010, how do I write an update query to "fill in the blanks"?

Here is a picture explaining what I have and what I'm looking for.
Tables and data describing the question.

This originally seemed like a very simple update query but it has proven I really don't understand how the query works. Can someone please explain the SQL I would need to move all the data from tblImport.Addtl_Initiator to the respective blanks under tblEntries.Initiator, and the data from tblImport.Addtl_Date_Initiated to the respective blanks under tblEntries.Date_Initiated? Essentially just moving the data to the left one column, but in a different table.

Additional information: There is a unique ID field on each of the tables not shown in the picture.

Answer Source

Try it with Nz.

UPDATE tblentries
INNER JOIN tblimport
ON tblentries.eid = tblimport.eid
AND tblentries.initiator = tblimport.initiator
AND tblentries.date_initiated = tblimport.date_initiated
SET tblentries.initiator = nz(tblimport.initiator, tblimport.addtl_initiator),
tblentries.date_initiated = nz(tblimport.date_initiated, import.addtl_date_initiated)

This link might also be helpful: coalesce alternative in Access SQL

Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download