Access 2007交叉表查询表达式 [英] Access 2007 Crosstab Query Expression

问题描述

目标:根据已计算列的值创建百分比列.

Goal: to create a percentage column based off the values of calculated columns.

以下是交叉表查询的SQL代码:

Here's the SQL code of the Crosstab query:

TRANSFORM Count(Master_Calendar.ID) AS CountOfID
SELECT Master_Calendar.Analyst, Count(Master_Calendar.ID) AS [Total Of ID]
FROM Master_Calendar
GROUP BY Master_Calendar.Analyst
PIVOT Master_Calendar.[Current Status];

这给了我一个交叉表查询，该查询显示了数据库中已完成"，处理中"或未启动"的条目数量，并按它们所属的分析师排序.

This gives me a crosstab query that displays the amount of entries in the database that are "Completed", "In Process", or "Not Started", sorted by which Analyst they belong to.

我想做的是添加另一列来计算完成百分比-因此(已完成/ID总数)*100.我尝试将其放入另一个单元格的表达式中，但返回的是"[已完成]，即使它在Expression Builder中作为选项提供给我也没有找到.

What I'm trying to do is add another column to calculate the Percent Complete -- so (Completed / Total of ID) * 100. I tried putting that into an expression in another cell, but it returns with a "[Completed]" not found, even though it gives me it as an option in the Expression Builder.

我只是将变量命名为错误，还是无法通过这种方式执行?我可以使用查询代码来引用包含已完成"的记录的总数，而不是使用数据透视表来查找值吗?

Am I just naming my variables wrong, or is it not possible to do it this way? Can I reference the total count of the records that contain "Completed" using query code instead of finding out the value using a Pivot table?

感谢您的帮助.

推荐答案

尝试:

SELECT 
    xTab.Analyst,
    [Completed]/([Total of ID]/100) AS [Complete%], 
    [In Process]/([Total of ID]/100) AS [In Process%],
    [Not Started]/([Total of ID]/100) AS [Not Started%]
FROM xTab;

这篇关于Access 2007交叉表查询表达式的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！