title: MS Access "double counting" in a query of queries
body: <p>Apologies if this question is a bit long, but I wanted to explain in detail what it is I am trying to do.</p>
  
  <p>I am developing a database in MS Access 2010/Windows 7 which analyses and reports on incidents (e.g. faults) in an organisation. An incident is reported as beginning at a particular date/time in a particular location for a particular duration. An incident may occasionally cause one or more "live resilience outages" (LRO) which will have the same start-time but can be in different locations and have different durations. So for example a router going out of service in the central technical area for 600 sec might cause live outages of 60 sec and 30 sec in studios 5 and 6 respectively.</p>
  
  <p>I need to report on three date ranges: the month in question, the previous month and the (financial, beginning in April) year to date. So for example the report for March 2012 would consider the periods 01 Mar 2012 - 31 Mar 2012 (month), 01 Feb 2012 - 29 Feb 2012 (previous) and 01 Apr 2011 - 31 Mar 2012 (YTD). </p>
  
  <p>These dates are correctly calculated in a form called ReportCentre. I have three queries to return the LROs for the different date ranges: <code>QueryLROMonth</code>, <code>QueryLROPrevious</code> and <code>QueryLROYTD</code> all of which work properly in isolation (i.e. return the correct values). So for example <code>QueryLROMonth</code> is defined as </p>
  
  <pre><code>SELECT lro.*
  FROM lro INNER JOIN incidents ON lro.pid = incidents.id
  WHERE (((incidents.begin) Between [Forms]![ReportCentre].[StartMonth] And 
           [Forms]![ReportCentre].[EndMonth]));
  </code></pre>
  
  <p>which returns the expected values:</p>
  
  <pre><code>id  pid duration    facility
  6   681   30            23
  7   686  857            23
  8   735  600            25
  9   738  600            25
  </code></pre>
  
  <p>as does the YTD query</p>
  
  <pre><code>id  pid duration    facility
  1   100 120     25
  2   366 5       25
  3   380 460     1
  4   505 341     23
  5   622 0       29
  6   681 30      23
  7   686 857     23
  8   735 600     25
  9   738 600     25
  20  1297    50      1
  </code></pre>
  
  <p><img src="https://i.imgur.com/Ht1xCDq.jpg" alt="A picture is worth a thousand words..."></p>
  
  <p>So far so good, but now the bit that's got me puzzled. I am trying to design another query which takes the output of the three LRO queries (and some other data), groups it all by facility and calculates things like availability. If I design a totals query and include the <code>Facilities</code> table (for the facility name) and the <code>QueryLROMonth</code> query e.g.</p>
  
  <pre><code>SELECT facilities.facility, Count(QueryLROMonth.id) AS lrocountmonth, Sum(QueryLROMonth.duration) AS lrosecondsmonth
  FROM QueryLROMonth INNER JOIN facilities ON QueryLROMonth.facility = facilities.ID
  GROUP BY facilities.facility;
  </code></pre>
  
  <p>This works fine and produces what I expect.</p>
  
  <pre><code>facility    lrocountmonth   lrosecondsmonth
  HQ3         2               887
  HQ5         2               1200
  </code></pre>
  
  <p>but as soon as I introduce the YTD query:</p>
  
  <pre><code>SELECT facilities.facility, Count(QueryLROMonth.id) AS lrocountmonth, Sum(QueryLROMonth.duration) AS lrosecondsmonth, Count(QueryLROYTD.id) AS lrocountytd, Sum(QueryLROYTD.duration) AS lrosecondsytd
  FROM QueryLROYTD INNER JOIN (QueryLROMonth INNER JOIN facilities ON QueryLROMonth.facility = facilities.ID) ON QueryLROYTD.facility = facilities.ID
  GROUP BY facilities.facility;
  </code></pre>
  
  <p>for some reason stuff starts being counted reported wrongly. Specifically the two <code>Count</code> columns are multiplied together and so <code>lrocountmonth</code> and <code>lrosecondsmonth</code> are both multiplied by <code>lrocountytd</code>. Similarly <code>lrocountytd</code> and <code>lrosecondsytd</code> are both multiplied by <code>lrocountmonth</code>.</p>
  
  <pre><code>facility    lrocountmonth   lrosecondsmonth lrocountytd lrosecondsytd
  HQ3     6       2661        6       2456
  HQ5     8       4800        8       2650
  </code></pre>
  
  <p>What am I doing wrong? How do I prevent this entanglement? </p>