Combining Update & Select SQL statements

MS Access VBA Scripts that create SQL Statements:

I have a SQL Statement that derives two fields in a new table SATable:

SQLScript = "SELECT [DATA OUTPUT TABLE].* INTO SATable"
SQLScript = SQLScript & " FROM [DATA OUTPUT TABLE]"
SQLScript = SQLScript & " WHERE ((([DATA OUTPUT TABLE].[Journal Voucher ID]) In (SELECT [DATA OUTPUT TABLE].[Journal Voucher ID]"
SQLScript = SQLScript & " FROM [DATA OUTPUT TABLE]"
SQLScript = SQLScript & " GROUP BY [DATA OUTPUT TABLE].[Journal Voucher ID]"
SQLScript = SQLScript & " HAVING (((First([DATA OUTPUT TABLE].dBranch))=" & Chr(34) & "Navy" & Chr(34) & ") AND ((Last([DATA OUTPUT TABLE].dBranch))=" & Chr(34) & "USMC" & Chr(34) & ")) OR (((First([DATA OUTPUT TABLE].dBranch))=" & Chr(34) & "USMC" & Chr(34) & ") AND ((Last([DATA OUTPUT TABLE].dBranch))=" & Chr(34) & "Navy" & Chr(34) & "));)));"
DoCmd.RunSQL SQLScript

Then I have a statement that takes those two derived fields and appends them onto the original table:

DoCmd.RunSQL "UPDATE SATable INNER JOIN [DATA OUTPUT TABLE] ON (SATable.[Journal Voucher ID] = [DATA OUTPUT TABLE].[Journal Voucher ID]) AND (SATable.dBranch = [DATA OUTPUT TABLE].dBranch) SET [DATA OUTPUT TABLE].[Shared Appropriations] = [SATable].[dBranch] & " & Chr(34) & " Shared Appropriation" & Chr(34) & ";"

My goal is to remove the SATable and perform this action in one statement.

I've struggled with problems like this before so instead of just providing an answer can you please provide some explanation - TEACH A MAN TO FISH!

Cheers, -E

EDIT:------------------------------------------------------------- The Data Output Table has:

JVID dBranch SA
   1  Navy   N/A
   2  Navy   N/A
   3  Navy   N/A
   4  Navy   N/A
   A  USMC   N/A
   B  USMC   N/A
   3  USMC   N/A
   4  USMC   N/A

So the SATable becomes:

JVID dBranch SA
   3  Navy   Navy SA
   3  USMC   USMC SA
   4  Navy   Navy SA
   4  USMC   USMC SA

Then the update query updates the SA field, which is N/A by default

JVID dBranch SA
   1  Navy   N/A
   2  Navy   N/A
   3  Navy   Navy SA
   4  Navy   Navy SA
   A  USMC   N/A
   B  USMC   N/A
   3  USMC   USMC SA
   4  USMC   USMC SA

Okay, so I might be making an assumption or two, but it looks like what you really want to do is to join [DATA OUTPUT TABLE] (@tDataOutputTable in my script) on itself (similar to what you're doing) and only update records that match on JVIDs, but not dBranch

Now, this will match any record where the dBranch columns don't match. If you only want this to happen for dBranch of 'Navy' and 'USMC', then you'll want to replace my d.dBranch <> d2.dBranch with something more like what you have.

Here is a SQL script that I created to test it:

DECLARE @tDataOutputTable TABLE
(
    JVID varchar(max),
    dBranch varchar(max),
    SA varchar(max)
)

INSERT INTO @tDataOutputTable (JVID, dBranch, SA)
VALUES 
('1', 'Navy', 'N/A'),
('2', 'Navy', 'N/A'),
('3', 'Navy', 'N/A'),
('4', 'Navy', 'N/A'),
('A', 'USMC', 'N/A'),
('B', 'USMC', 'N/A'),
('3', 'USMC', 'N/A'),
('4', 'USMC', 'N/A')

UPDATE d
    SET d.SA = d.dBranch + ' SA' -- or 'Shared Appropriation'
FROM @tDataOutputTable d
    INNER JOIN @tDataOutputTable d2 ON d.JVID = d2.JVID AND d.dBranch <> d2.dBranch

SELECT * FROM @tDataOutputTable

I'm not that familiar with the intricacies of VBA and Access, but hopefully something like this should work:

SQLScript = "UPDATE [DATA OUTPUT TABLE] AS d"
SQLScript = SQLScript & " INNER JOIN [DATA OUTPUT TABLE] AS d2 ON d.JVID = d2.JVID AND d.dBranch <> d2.dBranch"
SQLScript = SQLScript & " SET d.SA = d.dBranch" & Chr(34) & " Shared Appropriation" & Chr(34) & ";"
DoCmd.RunSQL SQLScript