title: Aggregate SQL query grouping by month
body: <p>I have a database of Transactions (Access 2007) that are recorded in hourly, daily and monthly intervals.  I would like to view them in a meaningful way (instead of hour-by-hour energy usage, which is how it's stored, I want all usage for the month of January, 2011).  The tables that I want to operate on have this general format:</p>
  
  <pre><code>CustID|CustomerCode|TransactionDate|(Transaction Hour depending on table)|Usage
  </code></pre>
  
  <p>So If I want to take a table like that and make a view that looked something like this</p>
  
  <pre><code>BillingPeriod|Usage(mWh)
  1/2011     |500
  2/2011     |600
  3/2011     |700
  etc
  </code></pre>
  
  <p>How would I go about doing that?  The transaction dates can be any date, and the transaction hours can be 1-24.  The query itself doesn't seem that hard, something along the lines of:</p>
  
  <pre><code>SELECT TransactionDate, SUM(Usage)
  FROM UsageTable
  Where (TransactionDate Between [Some Start Date] AND[Some End Date])
  GROUP BY TransactionDate;
  </code></pre>
  
  <p>The problem is formatting.  I obviously can't group by transactiondate for my desired results, I just wrote it so the query was semantically correct. Maybe I could do something like</p>
  
  <pre><code>SELECT Mid(TransactionDate,0,2) + Mid(TransactionDate, 6, 4)?
  </code></pre>
  
  <p>Any help would be appreciated</p>