update field to null value when there is no match in related table(s)

Question

I have 2 tables as shown below. I need to update the [incomming letter contractor] field of table [ASBUILT_LIST] with value of [TRANSMITTAL] of table [tblTransmittalls]

I have used the following update query

UPDATE ASBUILT_LIST INNER JOIN tblTransmittalls 
   ON ASBUILT_LIST.DOC=tblTransmittalls.DocumentNo 
SET ASBUILT_LIST.[incomming letter   contractor] = [tblTransmittalls]![TRANSMITTAL]
    WHERE  ((tblTransmittalls.PurposeofIssue)="FIN"));

Now when there is not any match for [DocumentNo] field (related between 2 tables) I need to update [incomming letter contractor] to NULL value.

SET ASBUILT_LIST.[incomming letter contractor] = Null

and when there is a match it is updated like before:

SET ASBUILT_LIST.[incomming letter contractor] = [tblTransmittalls]![TRANSMITTAL]

The tables are:

TABLE: ASBUILT_LIST

doc                 incomming letter contractor
------------------  ---------------------------
doc-1000            L-0001
doc-2000            L-0002

TABLE: tblTransmittalls

DocumentNo         TRANSMITTAL      PurposeofIssue
-----------------  ---------------  --------------
DOC-1000           T-0001           FIN
DOC-3000           T-0500           FIN
DOC-4000           T-0600           IFI
DOC-2000           T-0800           IFI

I need an updated ASBUILT_LIST table so after running update query it should be like this:

doc            incomming letter contractor
-------------  ---------------------------
doc-1000       T-0001   
doc-2000       null

score 1 · Accepted Answer · answered Oct 16 '13 at 07:03

1

If I understand your description correctly, this should be easy with DLookup().

UPDATE ASBUILT_LIST
SET [incomming letter contractor] = 
    DLookup
        (
            "TRANSMITTAL",
            "tblTransmittalls",
            "DocumentNo = '" & [DOC] & "' AND PurposeofIssue = 'FIN'"
        );

answered Oct 16 '13 at 07:03

HansUp

95,961
11
77
135

i did it and it works but problem is running update query is very slow, maybe because i have linked tables to other database. when i put the query and tables without link from other database in a separate database speed of running query is normal. but now is very slow. – masoud Oct 16 '13 at 12:36
Make sure to index `DocumentNo` in `tblTransmittalls`. I don't know if it would also help to index `PurposeofIssue`; try it with and without an index on that field. – HansUp Oct 16 '13 at 15:12

update field to null value when there is no match in related table(s)

1 Answers1