{"id":21,"date":"2008-06-20T16:21:26","date_gmt":"2008-06-20T15:21:26","guid":{"rendered":"http:\/\/muratyaman.co.uk\/wp\/?p=21"},"modified":"2020-04-01T13:08:24","modified_gmt":"2020-04-01T12:08:24","slug":"update-query-on-ms-access","status":"publish","type":"post","link":"https:\/\/www.muratyaman.co.uk\/blog\/index.php\/2008\/06\/update-query-on-ms-access\/","title":{"rendered":"Update Query on MS Access"},"content":{"rendered":"<p>Microsoft programmers are always very particular on their way of approaching problems. Since they are the &#8220;biggest&#8221; software developers in the world, they have to set the standards!?<\/p>\n<p>Problem: Updating field(s) of a table using a complex (sub) query.<\/p>\n<p>Solution:<\/p>\n<p>1. In standard SQL, you can this:<\/p>\n<pre lang=\"sql\" line=\"1\">\r\nUPDATE TABLE1 \r\nSET A_FIELD = Q.CALCULATED_FIELD\r\nFROM A_COMPLEX_QUERY Q\r\nWHERE TABLE1.ID_FIELD = Q.ID_FIELD;\r\n<\/pre>\n<p>It makes sense, right?<\/p>\n<p>No, not according to MS Access! <\/p>\n<p>2. You must try something like this:<\/p>\n<pre lang=\"sql\" line=\"1\">\r\nUPDATE TABLE1\r\nINNER JOIN A_COMPLEX_QUERY Q\r\nON TABLE1.ID_FIELD = Q.ID_FIELD\r\nSET A_FIELD = Q.CALCULATED_FIELD;\r\n<\/pre>\n<p>However, it says:<\/p>\n<p>&#8220;You must use an updatable query!&#8221;<\/p>\n<p>3. One of the &#8220;good&#8221; features of MS Access is its functions.<\/p>\n<pre lang=\"sql\" line=\"1\">\r\nUPDATE TABLE1 \r\nSET A_FIELD = \r\nDLOOKUP(\"CALCULATED_FIELD\", \"A_COMPLEX_QUERY\", \"ID_FIELD = \" & ID_FIELD);\r\n<\/pre>\n<p>Simple; but it may take <strong>ages<\/strong> execute if you have thousands of records.<br \/>\nYou can also write your own (public) functions in <strong>VBA modules<\/strong> and refer to them in anywhere inside your MS Access database.<\/p>\n","protected":false},"excerpt":{"rendered":"<p>Microsoft programmers are always very particular on their way of approaching problems. Since they are the &#8220;biggest&#8221; software developers in the world, they have to set the standards!?<\/p>\n","protected":false},"author":2,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[11],"tags":[15,33,17],"class_list":["post-21","post","type-post","status-publish","format-standard","hentry","category-technology","tag-access","tag-sql","tag-vba"],"_links":{"self":[{"href":"https:\/\/www.muratyaman.co.uk\/blog\/index.php\/wp-json\/wp\/v2\/posts\/21","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/www.muratyaman.co.uk\/blog\/index.php\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/www.muratyaman.co.uk\/blog\/index.php\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/www.muratyaman.co.uk\/blog\/index.php\/wp-json\/wp\/v2\/users\/2"}],"replies":[{"embeddable":true,"href":"https:\/\/www.muratyaman.co.uk\/blog\/index.php\/wp-json\/wp\/v2\/comments?post=21"}],"version-history":[{"count":3,"href":"https:\/\/www.muratyaman.co.uk\/blog\/index.php\/wp-json\/wp\/v2\/posts\/21\/revisions"}],"predecessor-version":[{"id":1012,"href":"https:\/\/www.muratyaman.co.uk\/blog\/index.php\/wp-json\/wp\/v2\/posts\/21\/revisions\/1012"}],"wp:attachment":[{"href":"https:\/\/www.muratyaman.co.uk\/blog\/index.php\/wp-json\/wp\/v2\/media?parent=21"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.muratyaman.co.uk\/blog\/index.php\/wp-json\/wp\/v2\/categories?post=21"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.muratyaman.co.uk\/blog\/index.php\/wp-json\/wp\/v2\/tags?post=21"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}