title: Cant figure out how to put Program Metrics in MSAccess
body: <p>So I thought I was doing good. I made 5 tables. tblComplexity, tblCoupling, tblLinesOfCode, tblMaintainIndex, and tblProjects</p>
  
  <p>tblProjects has 2 columns [ID, Project Name]</p>
  
  <p>the 4 other tables all have 3 columns the 3rd columns is the one that is different for each and is as you would guess based off of the name</p>
  
  <p>4 other tables [ID*, RunID, (complexity, coupling, lines, MI)]</p>
  
  <p>the 4 other tables only have numbers in them</p>
  
  <p>I put a many to 1 relation ship for tblProjects.ID and (4tables).RunID on each of the 4 tables.</p>
  
  <p>I then made a query for each this is qryComplexity</p>
  
  <pre><code>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;
  </code></pre>
  
  <p>all 4 of those queries give me the number I am after (I used to have this all in one excel sheet.)</p>
  
  <p>My goal in the end is to connect this database with excel, so that I can have my graphs. Problem is that this is what my excel sheet looks like (well one of the tabs)</p>
  
  <pre><code>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
  </code></pre>
  
  <p>and this is what my query returns</p>
  
  <pre><code>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
  </code></pre>
  
  <p>So i tried to add in the sum of my lines..and i get some crazy numbers. So how do I get my lines of code into each of my queries? Oh i guess i should put up my qryLinesOfCode</p>
  
  <pre><code>SELECT
     tblProjects.ProjectName,
     Sum(tblLinesOfCode.LinesOfCode) AS LinesOfCode  
  FROM
     tblProjects 
  INNER JOIN
     tblLinesOfCode 
        ON tblProjects.ID = tblLinesOfCode.RunID  
  GROUP BY
     tblProjects.ProjectName;
  </code></pre>
  
  <p>I thought it would be straight forward to just add another inner join to each of my 3 queries (yes 3..the 4th is for lines of code, but my excel sheet only has 3 tabs).. so i tried this</p>
  
  <pre><code>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;
  </code></pre>
  
  <p>but i get this</p>
  
  <pre><code>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
  </code></pre>
  
  <p>Can someone please help me out? Thank you</p>
  
  <p>PS. I'm using Access/Excel 2010</p>