无法弄清楚如何将程序指标放入 MSAccess

Question

所以我认为我做得很好。我做了5张桌子。tblComplexity， tblCoupling， tblLinesOfCode， tblMaintainIndex， and tblProjects

tblProjects 有 2 列 [ID， 项目名称]

其他 4 个表都有 3 列，第 3 列是每个不同的列，正如您根据名称猜测的那样

4 其他表 [ID*， 运行ID， （复杂度， 耦合， 线， MI）]

其他 4 个表中只有数字

我为 tblProjects.ID 和（4表）放了多对1的关系船。4 个表中每个表上的运行 ID。

然后我为每个查询了这是 qryComplexity

SELECT
   tblProjects.ProjectName,
   Round(Avg(tblComplexity.CyclomaticComplexity)+StDevP(tblComplexity.CyclomaticComplexity),2) AS MaxDeviation,
   Max(tblComplexity.CyclomaticComplexity) AS MaxOfCyclomaticComplexity,
   Min(tblComplexity.CyclomaticComplexity) AS MinOfCyclomaticComplexity,
   Round(Avg(tblComplexity.CyclomaticComplexity)-StDevP(tblComplexity.CyclomaticComplexity),2) AS MinDeviation  
FROM
   tblProjects 
INNER JOIN
   tblComplexity 
      ON tblProjects.ID = tblComplexity.RunID
GROUP BY
   tblProjects.ProjectName;

所有这 4 个查询都给了我想要的数字（我曾经在一个 Excel 工作表中拥有所有这些。

最后，我

的目标是将这个数据库与 excel 连接起来，这样我就可以拥有我的图表。问题是这就是我的 excel 工作表的样子（其中一个选项卡）

From Excel Maintainability Index               
            Lines   MaxDev  Max     Min     MinDev
Project1    332.00  94.83   100.00  70.00   72.64
Project2    2019.00 76.03   81.00   53.00   51.30
Project3    3052.00 87.37   100.00  42.00   62.57
Project4    576.00  94.88   100.00  69.00   77.25
Project5    1074.00 95.20   100.00  62.00   69.60
Project6    163.00  92.84   100.00  61.00   72.70
Project7    187.00  87.26   96.00   68.00   72.07
Project8    18.00   92.47   92.00   78.00   81.81
Project9    89.00   99.94   100.00  57.00   69.26
Project10   127.00  103.45  100.00  65.00   81.33

这就是我的查询返回的内容

From Access qryMaintiain
            MaxDev  Max     Min     MinDev
Project1    94.83   100.00  70.00   72.64
Project2    76.03   81.00   53.00   51.30
Project3    87.37   100.00  42.00   62.57
Project4    94.88   100.00  69.00   77.25
Project5    95.20   100.00  62.00   69.60
Project6    92.84   100.00  61.00   72.70
Project7    87.26   96.00   68.00   72.07
Project8    92.47   92.00   78.00   81.81
Project9    99.94   100.00  57.00   69.26
Project10   103.45  100.00  65.00   81.33

所以我试图添加我的行的总和。我得到了一些疯狂的数字。那么，如何将我的代码行放入我的每个查询中呢？哦，我想我应该把我的qryLinesOfCode放起来

SELECT
   tblProjects.ProjectName,
   Sum(tblLinesOfCode.LinesOfCode) AS LinesOfCode  
FROM
   tblProjects 
INNER JOIN
   tblLinesOfCode 
      ON tblProjects.ID = tblLinesOfCode.RunID  
GROUP BY
   tblProjects.ProjectName;

我认为向我的 3 个查询

中的每个查询添加另一个内部连接会很简单（是的，3..第 4 个用于代码行，但我的 excel 工作表只有 3 个选项卡）.. 所以我试了这个

SELECT
   tblProjects.ProjectName,
   Sum(tblLinesOfCode.LinesOfCode) AS LinesOfCode  
   Round(Avg(tblComplexity.CyclomaticComplexity)+StDevP(tblComplexity.CyclomaticComplexity),2) AS MaxDeviation,
   Max(tblComplexity.CyclomaticComplexity) AS MaxOfCyclomaticComplexity,
   Min(tblComplexity.CyclomaticComplexity) AS MinOfCyclomaticComplexity,
   Round(Avg(tblComplexity.CyclomaticComplexity)-StDevP(tblComplexity.CyclomaticComplexity),2) AS MinDeviation  
FROM
   ((tblProjects 
INNER JOIN
   tblComplexity 
      ON tblProjects.ID = tblComplexity.RunID)
INNER JOIN
   tblLinesOfCode 
      ON tblProjects.ID = tblLinesOfCode.RunID)  
GROUP BY
   tblProjects.ProjectName;

但我明白这个

ProjectName  Lines   MaxDev  Max  Min  MinDev
Project1    6057    94.83   100  70   72.64
Project2    5049    76.03   81   53   51.3  
Project3    201432  87.37   100  42   62.57
Project4    18432   94.88   100  69   77.25
Project5    32220   95.20   100  62   69.6
Project6    126     92.84   100  61   72.7
Project7    445     87.26   96   68   72.07
Project8    4980    92.47   92   78   81.81
Project9    12065   99.94   100  57   69.26
Project10   4238    103.45  100  65   81.33

有人可以帮我吗？谢谢

附言。我正在使用 Access/Excel 2010

Answer 1

呃。终于想通了。我不应该试图重新计算总和。相反，我使用了查询

SELECT
   tblProjects.ProjectName,
   qryLinesOfCode.LinesOfCode AS LinesOfCode,
   Round(Avg(tblComplexity.CyclomaticComplexity)+StDevP(tblComplexity.CyclomaticComplexity),2) AS MaxDeviation,
   Max(tblComplexity.CyclomaticComplexity) AS MaxOfCyclomaticComplexity,
   Min(tblComplexity.CyclomaticComplexity) AS MinOfCyclomaticComplexity,
   Round(Avg(tblComplexity.CyclomaticComplexity)-StDevP(tblComplexity.CyclomaticComplexity),2) AS MinDeviation  
FROM
   (tblProjects 
INNER JOIN
   tblComplexity 
      ON tblProjects.ID = tblComplexity.RunID
   ) 
INNER JOIN
qryLinesOfCode 
   ON tblProjects.ProjectName = qryLinesOfCode.ProjectName
GROUP BY
tblProjects.ProjectName,
LinesOfCode;