title: MS ACCESS SQL QUERY COUNT DISTINCT
body: <p>I understand how to do a count distinct when it's simple like this:</p>
  
  <pre><code>SELECT AllItemsDateRange.Processors.ACH_Processor, COUNT(*) AS NumDays
  FROM (SELECT DISTINCT AllItemsDateRange.Processors.ACH_Processor, AllItemsDateRange.SubmitDate FROM AllItemsDateRange)  AS T1
  GROUP BY AllItemsDateRange.Processors.ACH_Processor;
  </code></pre>
  
  <p>However, I'm not sure how to add a count distinct when it's a more complicated query without effecting the other data.  In the below query, I want the last item (NumDays) to be a distinct count of AllItemsDateRange.Processors.ACH_Processor, AllItemsDateRange.SubmitDate.</p>
  
  <pre><code>SELECT AllItemsDateRange.Processors.ACH_Processor, AllItemsDateRange.ExposureLimit, AllItemsDateRange.Footprint, Sum(IIf([AllItemsDateRange].[DebitAmount]&gt;0,1,0)) AS Debits, Sum(AllItemsDateRange.DebitAmount) AS DebitAmt, Sum(IIf([AllItemsDateRange].[CreditAmount]&gt;0,1,0)) AS Credits, Sum(AllItemsDateRange.CreditAmount) AS CreditAmt, Sum(IIf(([AllItemsDateRange].[ReturnDate]&gt;0) And ([AllItemsDateRange].[DebitAmount]&gt;0),1,0)) AS DebitReturns, Sum(IIf(([AllItemsDateRange].[ReturnDate]&gt;0) And ([AllItemsDateRange].[CreditAmount]&gt;0),1,0)) AS CreditReturns, Sum(IIf([AllItemsDateRange].[ReturnDate]&gt;0,[AllItemsDateRange].[CreditAmount]+[AllItemsDateRange].[DebitAmount],0)) AS ReturnAmt, Sum(IIf((([AllItemsDateRange].[ReturnCode]="R05") Or ([AllItemsDateRange].[ReturnCode]="R07") Or ([AllItemsDateRange].[ReturnCode]="R10") Or ([AllItemsDateRange].[ReturnCode]="R29") Or ([AllItemsDateRange].[ReturnCode]="R51")) And ([AllItemsDateRange].[DebitAmount]&gt;0),1,0)) AS UnauthorizedReturns, Sum(IIf((([AllItemsDateRange].[ReturnCode]="R05") Or ([AllItemsDateRange].[ReturnCode]="R07") Or ([AllItemsDateRange].[ReturnCode]="R10") Or ([AllItemsDateRange].[ReturnCode]="R29") Or ([AllItemsDateRange].[ReturnCode]="R51")) And ([AllItemsDateRange].[DebitAmount]&gt;0),[DebitAmount],0)) AS UnauthorizedReturnAmt, COUNT(AllItemsDateRange.SubmitDate) AS NumDays
      FROM AllItemsDateRange
      GROUP BY AllItemsDateRange.Processors.ACH_Processor, AllItemsDateRange.ExposureLimit, AllItemsDateRange.Footprint
      ORDER BY AllItemsDateRange.Footprint, AllItemsDateRange.ExposureLimit DESC , AllItemsDateRange.Processors.ACH_Processor DESC;
  </code></pre>
  
  <p><strong>EDIT</strong>:
  [AllItemsDateRange] is basically a list of transactions.  Each transaction has a date on it.  I want to summarize the data by ACH_Processor.  So getting the summary of of count and sum of debits and credits is easy enough.  The hard part is getting the count of distinct days on which a transaction was processed by that ACH_Processor so I can later calculate daily averages.</p>