在Access 2013中使用SQL更新查询 [英] Updating a Query with SQL in Access 2013

问题描述

我正在尝试使用SQL更新Microsoft Access中的查询.我在查询表达式中运行语法错误(缺少运算符)"时遇到以下错误，我已将以下代码添加到基本SELECT/FROM查询脚本的底部.

I am trying to update a Query in Microsoft Access using SQL. I am getting the following error when I run "Syntax error (missing operator) in query expression" I've added the following code to bottom of my basic SELECT/FROM Query script.

UPDATE [HRBI Query]
SET [HRBI Query].[PaySegmentMultiplier] = (CASE WHEN [PayGroupCountryDesc] = 'Country' AND BasePayRangeSegment = 'Above top segment' THEN 0 ELSE CASE
WHEN [PayGroupCountryDesc] = 'Country' AND BasePayRangeSegment = 'Below segment 1' THEN 1.35 ELSE CASE
WHEN [PayGroupCountryDesc] = 'Country' AND BasePayRangeSegment = 'S1' THEN 1.25 ELSE CASE
WHEN [PayGroupCountryDesc] = 'Country' AND BasePayRangeSegment = 'S2' THEN 1.15 ELSE CASE
WHEN [PayGroupCountryDesc] = 'Country' AND BasePayRangeSegment = 'S3' THEN .90 ELSE CASE
WHEN [PayGroupCountryDesc] = 'Country' AND BasePayRangeSegment = 'S4' THEN .60 ELSE CASE
WHEN [PayGroupCountryDesc] = 'Country' AND BasePayRangeSegment = 'S5' THEN .40 ELSE CASE
ELSE PaySegmentMultiplier
END
END
END
END
END
END) 
FROM [HRBI Query];

我是SQL新手.谁能解释我为什么收到此错误以及如何解决?谢谢.

I am new to SQL. Can anyone explain why I am receiving this error and how to fix? Thanks.

我尝试使用SWITCH，但是在sytax上仍然收到错误.有什么想法吗?

I tried using SWITCH, but am still receiving an error on sytax. Any ideas?

SELECT SWITCH([PayGroupCountryDesc] = 'Country' AND BasePayRangeSegment = 'Above top segment',[HRBI Query].PaySegmentMultiplier = 0,
[PayGroupCountryDesc] = 'Country' AND BasePayRangeSegment = 'Below segment 1', [HRBI Query].PaySegmentMultiplier =1.35,
[PayGroupCountryDesc] = 'Country' AND BasePayRangeSegment = 'S1', [HRBI Query].PaySegmentMultiplier =1.25,
[PayGroupCountryDesc] = 'Country' AND BasePayRangeSegment = 'S2', [HRBI Query].PaySegmentMultiplier =1.15,
[PayGroupCountryDesc] = 'Country' AND BasePayRangeSegment = 'S3', [HRBI Query].PaySegmentMultiplier =.90,
[PayGroupCountryDesc] = 'Country' AND BasePayRangeSegment = 'S4', [HRBI Query].PaySegmentMultiplier =.60,
[PayGroupCountryDesc] = 'Country' AND BasePayRangeSegment = 'S5', [HRBI Query].PaySegmentMultiplier =.40, True,'Error') 
FROM [HRBI Query];

我尝试了这个并且仍然出现语法错误?

I tried this and still syntax error?

SET [HRBI Query].[PaySegmentMultiplier] = Switch (
[PayGroupCountryDesc] = 'Country' AND BasePayRangeSegment = 'Above top segment', 0, 
[PayGroupCountryDesc] = 'Country' AND BasePayRangeSegment = 'Below segment 1', 1.35, 
[PayGroupCountryDesc] = 'Country' AND BasePayRangeSegment = 'S1', 1.25, 
[PayGroupCountryDesc] = 'Country' AND BasePayRangeSegment = 'S2', 1.15, 
[PayGroupCountryDesc] = 'Country' AND BasePayRangeSegment = 'S3', .90, 
[PayGroupCountryDesc] = 'Country' AND BasePayRangeSegment = 'S4', .60, 
[PayGroupCountryDesc] = 'Country' AND BasePayRangeSegment = 'S5', .40, TRUE, PaySegmentMultiplier);

推荐答案

在MS Access中，CASE/WHEN的最佳翻译是嵌套的

In MS Access, the best translation of CASE/WHEN would be the nested IIF() function. Also in update SQL statements with Access' dialect, you would not use the FROM clause at end.

唯一的挑战是跟踪应该等于IIF数量的括号.我在下面缩进以帮助可视化:

The only challenge is keeping track of parentheses which should equal the number of IIFs. I indent below to help visualize:

UPDATE [HRBI Query]
SET [HRBI Query].[PaySegmentMultiplier] = 
IIF([PayGroupCountryDesc] = 'Country' AND BasePayRangeSegment = 'Above top segment', 0,
   IIF([PayGroupCountryDesc] = 'Country' AND BasePayRangeSegment = 'Below segment 1', 1.35,
      IIF([PayGroupCountryDesc] = 'Country' AND BasePayRangeSegment = 'S1', 1.25,
        IIF([PayGroupCountryDesc] = 'Country' AND BasePayRangeSegment = 'S2', 1.15,
           IIF([PayGroupCountryDesc] = 'Country' AND BasePayRangeSegment = 'S3', .90, 
              IIF([PayGroupCountryDesc] = 'Country' AND BasePayRangeSegment = 'S4', .60, 
                 IIF([PayGroupCountryDesc] = 'Country' AND BasePayRangeSegment = 'S5', .40, 
                     PaySegmentMultiplier
                 )
              )
           )
        )
     )
  )
);

这篇关于在Access 2013中使用SQL更新查询的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！