访问 2010 SQL 查询死胡同

Question

我需要为一个项目创建一个数据库。在过去的几个月里，我一直在学习MS Access 2010的基础知识，并开始涉足SQL进行更具体的查询。话虽如此，我对程序的功能有一些非常基本的了解和知识。

我正在尝试从单个表创建查询以显示一系列计算。我已经取得了很大的进步，但现在我挠头，并寻求一些帮助。

Table: LIT_ALL
SITE  DATE    TRANSECT  CATEGORY  DISTANCE_M
PIT  6/2/2011     1        RKC      0.25
PIT  6/2/2011     1        FMA      0.58
PIT  6/2/2011     1        LHC      0.75
PIT  6/2/2011     2        FMA      0.5
PIT  6/2/2011     2        SA       0.6
PIT  6/2/2011     2        RKC      0.52
PIT  6/2/2011     2        LHC      0.6
PIT  6/2/2011     3        CCA      0.59
PIT  6/2/2011     3        LHC      0.6
PIT  6/2/2011     3        FMA      0.52

使用此代码

TRANSFORM Sum(DISTANCE_M) AS SumOfDISTANCE_M
SELECT SITE, DATE, [CATEGORY]
FROM LIT_ALL
GROUP BY SITE, DATE,[CATEGORY]
PIVOT TRANSECT;

我的最终产品有部分格式

SITE  DATE      CATEGORY   1    2    3 
PIT   6/2/2011  CCA                  .59
PIT   6/2/2011  FMA       .58  .5    .52
PIT   6/2/2011  LHC       .75  .6    .6
PIT   6/2/2011  RKC       .25  .52    
PIT   6/2/2011  SA             .6

除了一些死胡同，我没能产生这样的结果。

SITE    DATE     CATEGORY   1      2      3     AVG    STD    SE
PIT    6/2/2011  CCA       0.00   0.00   34.5   11.5   19.92  6.64
PIT    6/2/2011  FMA       36.71  22.52  30.41  29.88  7.11   2.37
PIT    6/2/2011  LHC       47.47  27.03  35.09  36.53  10.30  3.43
PIT    6/2/2011  RKC       15.82  23.42  0.00   13.08  11.95  3.98
PIT    6/2/2011  SA        0.00   27.03  0.00   9.01   15.60  5.20

此示例中显示的值是百分比值。1,2,3 字段（透视横断面字段）中的每个值都通过字段 1、2 或 3/总和（分别为 1、2 或 3 个值）*100 中的单元格值进行格式化。我在删除 Null 时也遇到了问题。我已经使用Nz函数取得了一定程度的成功。只有我让我的 AVG 不考虑新插入的 O 值并扔掉 AVG。 STDEV 是你每天运行的工厂流行 stdev。SE 表示错误。.在本例中为 STDEV/3（即横断面总数）。感谢您的任何和所有帮助，为我指明正确的方向。我每天都在学习新技巧！乐于接受建议

Answer 1

创建 2 个查询，其中一个是您的透视。第二个查询是标准选择，其中包含 avg、stdev 和 se 的聚合。然后创建第三个查询，用于将数据透视查询联接到聚合。我更改了一些字段名称只是'原因。

qryPivot

 TRANSFORM Sum(LIT_ALL.DISTANCE) AS SumOfDISTANCE
 SELECT LIT_ALL.SITE, LIT_ALL.dtDATE, LIT_ALL.[CATEGORY]
 FROM LIT_ALL
 GROUP BY LIT_ALL.SITE, LIT_ALL.dtDATE, LIT_ALL.[CATEGORY]
 PIVOT LIT_ALL.TRANSECT;

qryStats

 SELECT LIT_ALL.Site, LIT_ALL.dtDate, LIT_ALL.category, Avg(LIT_ALL.distance) AS Mean,
 StDev(LIT_ALL.distance) AS SD, StDev([distance])/3 AS SE
 FROM LIT_ALL
 GROUP BY LIT_ALL.Site, LIT_ALL.dtDate, LIT_ALL.category;

qry结果

SELECT qryPivot.*, qryStats.Mean, qryStats.SD, qryStats.SE
FROM qryPivot INNER JOIN qryStats ON (qryPivot.CATEGORY = qryStats.category) AND 
(qryPivot.dtDATE =   qryStats.dtDate) AND (qryPivot.SITE = qryStats.Site);

因为枢轴列名称可能并不总是已知的，所以我使用 select *。