1

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.

UnseenSpecter
  • 68
  • 8

2 Answers2

0

Use IsNull to get the non-blank values:

SELECT
     IsNull(Initiator, Addtl_Inititator) AS Initiator,
     IsNull(Date_Initiated, Addtl_Date_Initiated) AS Date_Initiated,
FROM
     tblImport
Tedo G.
  • 1,556
  • 3
  • 17
  • 29
0

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

Community
  • 1
  • 1
kjmerf
  • 4,275
  • 3
  • 21
  • 29
  • I get the error "Circular reference caused by alias 'Initiator' in query definition's SELECT list." – UnseenSpecter Oct 05 '16 at 14:56
  • Now try. Updated the alias names following AS – kjmerf Oct 05 '16 at 15:01
  • Great! That correctly queries the data into three fields. I'm not entirely sure why that worked differently than your previous code... Now how would I go about updating tblEntries to match the query? – UnseenSpecter Oct 05 '16 at 15:05
  • Give it a try now. I made it into a UPDATE statement. Made a copy of the entries table before you run it though in case it doesn't work! – kjmerf Oct 05 '16 at 15:42
  • It did update the Initiator column of tblEntries, but I'm not entirely sure what logic it used because it updated all of the fields (not just blanks), and the names just appear to be random. Also, the dialog to confirm the update says it is going to update about 9 times as many rows as I would expect, which is also almost 3 times as many rows as tblEntries has! When I look at the query in datasheet view, I just see tons of blank fields with a few rows populated fields grouped together throughout. I reset tblEntries to the backup copy. – UnseenSpecter Oct 05 '16 at 16:28
  • Ok. I think the problem is with the join. I was under the assumption that EID is a unique value for each row, but it looks like it actually not. Is there a unique identifier for each row? Then we would just have to update the "ON" clause – kjmerf Oct 05 '16 at 17:13
  • The only Unique identifier for tblEntries and tblImport is the default ID field, which is also the primary key. tblEntries is detailed breakdown of information for a different table based on EID (we'll call it tblCompanies). There is a one (tblCompanies) to many (tblEntries) relationship between the tables joining EID. EID is the unique, primary key for tblCompanies. Not sure if that context helps at all. – UnseenSpecter Oct 05 '16 at 17:22
  • Wow. I'm so dumb. I linked the tables using the unique ID field (which previously didn't really mean anything to me). Now the update query works correctly... thank you! I cannot believe I missed that. – UnseenSpecter Oct 05 '16 at 17:32
  • Alright! There you go! Glad we got it. I added some AND statements in there that I think would also might do the trick – kjmerf Oct 05 '16 at 17:36