title: Distinct Count with two tables
body: <p>I have two tables on Access, Customer and Transaction. I'm trying to find the total # of transactions sorted by Dogs (0-3). The Transaction table has a line for each item bought, so multiple lines can be for one TransactionID.</p>
  
  <p>Here's what I have so far:</p>
  
  <pre><code>SELECT Customer.Dogs, COUNT(Transaction.TransactionID) AS TotTrans
  FROM Transaction, Customer
  WHERE Transaction.CustomerID = Customer.CustomerID
  GROUP BY Dogs
  </code></pre>
  
  <p>And I get</p>
  
  <pre><code>Dogs | TotTrans
  0    | 130104
  1    | 59132
  2    | 17811
  3    | 1401
  </code></pre>
  
  <p>Obviously this counts the total rows in the Transaction Table and sorts them by # of dogs. However, it is counting for the duplicates in the Transaction Table (e.g. There are three rows with TransactionID = 2, because in that transaction the customer bought 3 items. The Count is obviously including the extra 2 rows).</p>
  
  <p>When I try to do <code>COUNT(DISTINCT Transaction.TransactionID)</code> it doesn't work, and the message </p>
  
  <blockquote>
    <p>"Syntax error (missing operator) in query expression 'COUNT(DISTINCT Transaction.TransactionID)'.</p>
  </blockquote>
  
  <p>I have looked around, but can't seem to find the solution. I think part of the problem stems from the fact that I'm selecting two attributes.</p>
  
  <p>If anyone could help explain what to do and the logic behind it, that would be great!</p>