2

I understand how to do a count distinct when it's simple like this:

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;

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.

SELECT AllItemsDateRange.Processors.ACH_Processor, AllItemsDateRange.ExposureLimit, AllItemsDateRange.Footprint, Sum(IIf([AllItemsDateRange].[DebitAmount]>0,1,0)) AS Debits, Sum(AllItemsDateRange.DebitAmount) AS DebitAmt, Sum(IIf([AllItemsDateRange].[CreditAmount]>0,1,0)) AS Credits, Sum(AllItemsDateRange.CreditAmount) AS CreditAmt, Sum(IIf(([AllItemsDateRange].[ReturnDate]>0) And ([AllItemsDateRange].[DebitAmount]>0),1,0)) AS DebitReturns, Sum(IIf(([AllItemsDateRange].[ReturnDate]>0) And ([AllItemsDateRange].[CreditAmount]>0),1,0)) AS CreditReturns, Sum(IIf([AllItemsDateRange].[ReturnDate]>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]>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]>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;

EDIT: [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.

tgro
  • 35
  • 1
  • 5
  • What are you wanting a distinct count of in the 2nd query, just the ACH_Processor count(*) as number of days? – xQbert Jan 17 '14 at 21:22
  • http://stackoverflow.com/questions/11880199/how-do-i-count-unique-items-in-field-in-access-query – xQbert Jan 17 '14 at 21:29
  • I basically want the first query included in the second query results (in place of NumDays). Does that make sense? – tgro Jan 21 '14 at 12:54

2 Answers2

0

count(Distinct fieldname) Feature not supported in access: you have to do this to get distinct: How do I count unique items in field in Access query?

Community
  • 1
  • 1
xQbert
  • 34,733
  • 2
  • 41
  • 62
  • I know how to get a distinct count. I'm just having trouble including that count in another, more complicated, query. Thank you though. – tgro Jan 21 '14 at 12:59
0

Revise the first query to produce the distinct count (NumDays) for the same GROUP BY as the second query.

Remove NumDays from the second query.

Then you can INNER JOIN the two on those fields which were included in the GROUP BY.

But reading the question again, it sounds like you actually do want a different GROUP BY for each query. In that case, base the join on the field(s) which are included in both: ACH_Processor

That can still work, but the same NumDays value could be repeated across more than one row of the final query. But maybe that's what you want ...

HansUp
  • 95,961
  • 11
  • 77
  • 135
  • I think you're on to something. I'm not sure I fully understand how to implement this. My problem might be that AllItemsDateRange is already a query that is joining multiple tables. So, writing a query based on a query might be making things too complicated. I may need to step back and rethink the table structure. Basically, I'm trying to write this query because I was using a report to combine all of this data using DLOOKUP for some of the fields (e.g. - NumDays) and it took far too long to generate. This query is coming up in just a few seconds. – tgro Jan 21 '14 at 13:05
  • I took your advice of making the first query as close to the second query in structure as possible and then used an INNER JOIN. It seems to have worked. Thanks for your help! – tgro Jan 21 '14 at 15:50