Count Distinct Performance Issue

[Ferruccio73]

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.

The table looks something like this:
Amount, PO_Num,VendorName
$2000,1234,ABC
$3000,1234,ABC
$4000,1200,ABC
$6000,4321,BAC
$1000,4300,BAC
$2000,3214,CAB

What I'm trying to do is get a list of Vendors and the number of purchase orders they have over $2500.

It should return something like:
Vendor,Number_Of_POs
ABC,2
BAC,1


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;

Any help is greatly appreciated.

 

[Scooterbug]

Design a query with the 3 fields. Set the criteria for the amount to >$2500. Then, click on the sigma (the Uppercase E on the menu bar). That will add a new line (called Total) to the fields. Add the Purchase order field again and set the new field to Count. Also change the Total for Amount field to Where.

That should give you a count of the Vendors who meet the criteria

 

[Mr. B]

Ferruccio73,

Try the sql statement below:

SELECT A.VendorName, Count(A.PO_Num)
AS CountOfPO_Num
FROM A
WHERE (((A.Amount)>2500))
GROUP BY A.VendorName;


This will return exactly what you want.

Of course change A to the name of your table.

 

[Ferruccio73]

Thank you both for the quick response, but Dr. B this did not return a count of distinct purchase orders. It counted orders multiple times. It also did not sum the amount fields to ensure that they totalled more than $2500. Any thoughts on how I can modify this to ensure I'm only getting a distinct purchase order count of orders that totalled more than $2500?

Thanks again.

Scooterbug,

When I hit the Sigma it did not add a new line called Total.

 

[Scooterbug]

Did up a quick little example based on the data you provided. Check out Query1

 

[Ferruccio73]

I took a look at query 1 and I think we're getting closer. The problem with this query is that it is returning ABC twice.

What I need it to return is:
ABC, 2
BAC, 1

I tried this:
SELECT B.VendorName AS Vendor, Count(B.PO_Num) AS Number_Of_POs
FROM (SELECT DISTINCT PO_Num, VendorName FROM Cons_Data) AS B
GROUP BY B.VendorName
ORDER BY Count(B.PO_Num) DESC;

This returns Vendors with a full count of purchase orders. What I need it to do is filter out POs under $2500. Can I add that in B somewhere?

 

[Mr. B]

Ok, try this:

First, create the following query:
SELECT tblPOsByVendor.Amount, tblPOsByVendor.PO_Num, tblPOsByVendor.VendorName
FROM tblPOsByVendor
WHERE (((tblPOsByVendor.Amount)>2500));

And save it as "qryPOGreaterThan2500".

Next create this query:
SELECT qryPOGreaterThan2500.VendorName, Count(qryPOGreaterThan2500.PO_Num) AS CountOfPO_Num, Sum(qryPOGreaterThan2500.Amount) AS SumOfAmount
FROM qryPOGreaterThan2500
GROUP BY qryPOGreaterThan2500.VendorName;

As you can see, you are now using the first query which restricts records to return only records where the PO value is > 2500

The second query does the grouping and summing.

This produces:
VendorName Count Amount
ABC 2 7,000
BAC 1 6,000

 

[Scooterbug]

Or you can just modify Query1. Use this sql:

SELECT tblPurchaseOrders.VendorName, Count(tblPurchaseOrders.PO_Number) AS CountOfPO_Number
FROM tblPurchaseOrders
WHERE (((tblPurchaseOrders.Amount)>2500))
GROUP BY tblPurchaseOrders.VendorName;

That will count up the number of POs over 2500 for each company.

 

[Ferruccio73]

Dr. B,

The query you gave me did not provide a distinct count of pos, but you lead me down the right path. This is what I did and I validated the data to be correct.

First Query called qry_POGreaterThan2500...
SELECT PO_Num, VendorName, SUM(AmountDC) AS PO_Total
FROM Cons_Data
Group By VendorName, PO_Num
HAVING SUM(AmountDC)>2500;

Second Query....
SELECT qry_POGreaterThan2500.VendorName, Count(qry_POGreaterThan2500.PO_Num) AS CountOfPO_Num
FROM qry_POGreaterThan2500
GROUP BY qry_POGreaterThan2500.VendorName
ORDER BY Count(qry_POGreaterThan2500.PO_Num) DESC;

This returns two columns..The first with the name and the second with the number of pos with a total of over $2500. I wish I could add total the the pos, but this should probably get me what I need. Thank you both so much for the help. I wouldn't have gotten it without you guys.

Take Care
Ferruccio