更新查询、嵌套iif集和联接表

ivqmmu1c  于 2021-08-01  发布在  Java
关注(0)|答案(1)|浏览(434)

尝试使用内部联接进行更新查询，并使集合反映当前月数。
需要语法帮助来更新包含月份列的联接表，需要更新月份（date（））+1，+2。尝试使用下面嵌套的iif作为“iif（month（date（））=”1”，（set table.c=table2.c2……）完成下面的代码。
结果是语法错误
使用用于更新的值更新sql tablelinked excel的示例数据

  1. Update dbo_MasterSalesForecast_Test
  3. INNER JOIN MasterSalesForecastUpdate
  5. ON (dbo_MasterSalesForecast_Test.SubTo=MasterSalesForecastUpdate.Item) AND( dbo_MasterSalesForecast_Test.Planner=MasterSalesForecastUpdate.Planner)
  7. IIF(Month(Date()) ="1",( SET
  9. dbo_MasterSalesForecast_Test.[YY:1] = [MasterSalesForecastUpdate].[YYJan],
  11. dbo_MasterSalesForecast_Test.[YY:2] = [MasterSalesForecastUpdate].[YYFan],
  13. dbo_MasterSalesForecast_Test.[YY:3] = [MasterSalesForecastUpdate].[YYMar])
  15. ,
  17. IIF(Month(Date())="2",( SET
  19. dbo_MasterSalesForecast_Test.[YY:2]=[MasterSalesForecastUpdate].[YYFeb],
  21. dbo_MasterSalesForecast_Test.[YY:3]=[MasterSalesForecastUpdate].[YYMar],
  23. dbo_MasterSalesForecast_Test.[YY:4]=[MasterSalesForecastUpdate].[YYApr])
  25. ,
  27. IIF(Month(Date())="3",( SET
  29. dbo_MasterSalesForecast_Test.[YY:3]=[MasterSalesForecastUpdate].[YYMar],
  31. dbo_MasterSalesForecast_Test.[YY:4]=[MasterSalesForecastUpdate].[YYApr],
  33. dbo_MasterSalesForecast_Test.[YY:5]=[MasterSalesForecastUpdate].[YYMay])
  35. ,
  37. IIF(Month(Date())="4",(SET
  39. dbo_MasterSalesForecast_Test.[YY:4]=[MasterSalesForecastUpdate].[YYApr],
  41. dbo_MasterSalesForecast_Test.[YY:5]=[MasterSalesForecastUpdate].[YYMay],
  43. dbo_MasterSalesForecast_Test.[YY:6]=[MasterSalesForecastUpdate].[YYJun])
  45. ,
  47. IIF(Month(Date())="5",(SET
  49. dbo_MasterSalesForecast_Test.[YY:5]=[MasterSalesForecastUpdate].[YYMay],
  51. dbo_MasterSalesForecast_Test.[YY:6]=[MasterSalesForecastUpdate].[YYJun],
  53. dbo_MasterSalesForecast_Test.[YY:7]=[MasterSalesForecastUpdate].[YYJul])
  55. ,
  57. IIF(Month(Date())="6",(SET
  59. dbo_MasterSalesForecast_Test.[YY:6]=[MasterSalesForecastUpdate].[YYJun],
  61. dbo_MasterSalesForecast_Test.[YY:7]=[MasterSalesForecastUpdate].[YYJul],
  63. dbo_MasterSalesForecast_Test.[YY:8]=[MasterSalesForecastUpdate].[YYAug])
  65. ,
  67. IIF(Month(Date())="7",(SET
  69. dbo_MasterSalesForecast_Test.[YY:7]=[MasterSalesForecastUpdate].[YYJul],
  71. dbo_MasterSalesForecast_Test.[YY:8]=[MasterSalesForecastUpdate].[YYAug],
  73. dbo_MasterSalesForecast_Test.[YY:9]=[MasterSalesForecastUpdate].[YYSep])
  75. ,
  77. IIF(Month(Date())="8",(SET
  79. dbo_MasterSalesForecast_Test.[YY:8]=[MasterSalesForecastUpdate].[YYAug],
  81. dbo_MasterSalesForecast_Test.[YY:9]=[MasterSalesForecastUpdate].[YYSep],
  83. dbo_MasterSalesForecast_Test.[YY:10]=[MasterSalesForecastUpdate].[YYOct])
  85. ,
  87. IIF(Month(Date())="9",(SET
  89. dbo_MasterSalesForecast_Test.[YY:9]=[MasterSalesForecastUpdate].[YYSep],
  91. dbo_MasterSalesForecast_Test.[YY:10]=[MasterSalesForecastUpdate].[YYOct],
  93. dbo_MasterSalesForecast_Test.[YY:11]=[MasterSalesForecastUpdate].[YYNov])
  95. ,
  97. IIF(Month(Date())="10",(SET
  99. dbo_MasterSalesForecast_Test.[YY:10]=[MasterSalesForecastUpdate].[YYOct],
  101. dbo_MasterSalesForecast_Test.[YY:11]=[MasterSalesForecastUpdate].[YYNov],
  103. dbo_MasterSalesForecast_Test.[YY:12]=[MasterSalesForecastUpdate].[YYDec])
  105. ,
  107. IIF(Month(Date())="11",(SET
  109. dbo_MasterSalesForecast_Test.[YY:11]=[MasterSalesForecastUpdate].[YYNov],
  111. dbo_MasterSalesForecast_Test.[YY:12]=[MasterSalesForecastUpdate].[YYDec],
  113. dbo_MasterSalesForecast_Test.[YY+1:1]=[MasterSalesForecastUpdate].[YY+1Jan])
  115. ,
  117. IIF(Month(Date())="12"(SET
  119. dbo_MasterSalesForecast_Test.[YY:12]=[MasterSalesForecastUpdate].[YYDec],
  121. dbo_MasterSalesForecast_Test.[YY+1:1]=[MasterSalesForecastUpdate].[YY+1Jan],
  123. dbo_MasterSalesForecast_Test.[YY+1:02]=[MasterSalesForecastUpdate].[YY+1Feb])
  125. )))))))))));
sqlms-access

1条答案

按热度按时间
6ss1mwsb

6ss1mwsb1#

不能有条件地引用字段。字段在查询对象中必须是静态的，然后可以在iif（）中为每个字段确定要更新的值。使用vba构建和执行action sql语句。

  1. CurrentDb.Execute "UPDATE dbo_MasterSalesForecast_Test " & _
  2.      "INNER JOIN MasterSalesForecastUpdate " & _
  3.      "ON (dbo_MasterSalesForecast_Test.SubTo=MasterSalesForecastUpdate.Item) " & _
  4.           "AND (dbo_MasterSalesForecast_Test.Planner=MasterSalesForecastUpdate.Planner) " & _
  5.      "SET [YY:" & Month(Date()) & "] = [YY" & Format(Date(),"mmm") & "]," & _
  6.          "[YY:" & Month(DateAdd("m",1,Date())) & "] = [YY" & Format(DateAdd("m",1,Date()),"mmm") & "]," & _
  7.          "[YY:" & Month(DateAdd("m",2,Date())) & "] = [YY" & Format(DateAdd("m",2,Date()),"mmm") & "]"
赞(0）分享  回复(0）举报  2021-08-01
我来回答

相关问题

    查看更多

    热门标签

    更多
    JavaquerypythonNode开发语言requestUtil数据库Table后端算法LoggerMessageElementParser

    最新问答

    更多
    • 蜀ICP备13028337号-1 大数据知识库 https://www.saoniuhuo.com © All rights reserved
    • 本站内容来源互联网,如果侵犯您的权益请联系我们删除, 联系方式：448109455@qq.com