仅在MS Access 2003中的某个客户进行求和 [英] Summing only if a certain customer in MS Access 2003

问题描述

我想使用案例陈述（MS Access无法识别），但这就是我所拥有的：

I wanted to use a case statement (not recognised by MS Access) but this is what I have instead:

Sum(if [Time Report].Cust_id=2
                          then ([Time Report].[Hrs P1]) 
                          ELSE 0 END IF) AS [SumOfHrs P1],  
Sum(if [Time Report].Cust_id=2
                          then ([Time Report].[Hrs P2]) 
                          ELSE 0 END IF) AS [SumOfHrs P2], 
Sum(if ([Time Report].Cust_id=2 )
                          then ([Time Report].[Hrs P3]) 
                          ELSE 0 END IF) AS [SumOfHrs P3], 

等。

我想要的是一个新表，其中只包含'pn for cust_id = 2 $的摘要b $ b使用这种布局

etc.
What I want is a new table with just the summaries of the 'Pn's for cust_id = 2 With this sort of layout

Name    P1    P2    .....
Bob     0.23  0.45

但我在查询表达式'query'中收到错误消息语法错误（缺少运算符） ，然后它突出显示我的查询的 THEN 部分。

But I get the error message Syntax error (missing operator) in query expression 'query', and then it highlights the THEN part of my query.

出了什么问题？

Ps：将sql写入任何东西，但访问2003不可能是不可能的。

P.s: Writing the sql in anything but access 2003 is not going to be possible unfortuantely.

推荐答案

<我想你想要：

Sum(iif ([Time Report].Cust_id=2,[Time Report].[Hrs P1],0)) AS [SumOfHrs P1],  
Sum(iif ([Time Report].Cust_id=2,[Time Report].[Hrs P2],0)) AS [SumOfHrs P2], 
Sum(iif ([Time Report].Cust_id=2,[Time Report].[Hrs P3],0)) AS [SumOfHrs P3], 

参见 http://office.microsoft.com/en-us/access-help/iif-function-HA001228853.aspx

这篇关于仅在MS Access 2003中的某个客户进行求和的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！