条件过滤的MS-Access SQL困难

I have a query in MS-Access that I am trying to pull some values based on a condition, then sum the results.

我在MS-Access中有一个查询,我试图根据条件提取一些值,然后对结果求和。

SELECT SchedulingLog.UserID, SchedulingLog.Category, Sum(SchedulingLog.Value) AS Gain, Sum(SchedulingLog.Value) AS Used, [Gain]+[Used] AS [Left]
FROM SchedulingLog
GROUP BY SchedulingLog.UserID, SchedulingLog.Category, [Gain]+[Used]
HAVING ((
(SchedulingLog.Category) Like "DH*" Or 
(SchedulingLog.Category) Like "Com*") AND 
("Where[CatDetai]" Like "Gain*") AND 
("Where[CatDetai]" Like "Used*")
);

With these data in SchedulingLog ...

在SchedulingLog中使用这些数据...

Userid LogDate EventDate Category CatDetail Value
abc123  1-1-11    7-2-11 DH       DH Used      -1
abc123  1-1-11    7-4-11 DH       DH Gain       1

... I want my query to give me this result set:

...我希望我的查询给我这个结果集:

Userid Category Gain Used Left
abc123 DH          1   -1    0

1 个解决方案

#1

The following SQL returns what you asked for based on testing with your sample data in Access 2007.

以下SQL根据在Access 2007中使用示例数据进行测试,返回您要求的内容。

SELECT
    Userid,
    Category,
    Sum(IIf(CatDetail ALike '%Gain', [Value], 0)) AS Gain,
    Sum(IIf(CatDetail ALike '%Used', [Value], 0)) AS Used,
    [Gain] + [Used] AS [Left]
FROM SchedulingLog
GROUP BY
    Userid,
    Category

However, if you don't actually need to see the separate values for Gain and Used, it would be simpler to do it this way:

但是,如果您实际上不需要查看Gain和Used的单独值,则以这种方式执行此操作会更简单:

SELECT
    Userid,
    Category,
    Sum([Value]) AS [Left]
FROM SchedulingLog
GROUP BY
    Userid,
    Category

#1