Hello and welcome to our community! Is this your first visit?
Register
Enjoy an ad free experience by logging in. Not a member yet? Register.
Results 1 to 1 of 1
  1. #1
    New to the CF scene
    Join Date
    Oct 2009
    Posts
    1
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Access DB Count Distinct Performance Issue

    I wrote the query below, but it's taking over an hour to complete. I understand that access does not have the count(distinct x) function, so I was lead down this path. Any idea if this is wrong or if I can improve its performance. Any assistance would be greatly appreciated.

    SELECT A.VendorName AS Vendor, Count(B.PO_Num) As Number_Of_POs
    FROM Data AS A,(Select Distinct PO_Num, VendorName from Data) as B
    GROUP BY A.VendorName
    HAVING SUM(A.Amount)>2500
    ORDER BY Count(B.PO_Num) Desc;

    What I would like it to return data in the following form:
    Vendor,#ofDistinctPO
    ABC,23
    Last edited by ferruccio73; 10-27-2009 at 08:27 PM.


 

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •