Help with Aggregate Query - Count Distinct

[eerich]

Hello,

I'm having trouble with a MS Access query where I'm trying to retrieve the total invoice amounts for vendors that have addresses in multiple states. I receive an error message when trying to execute the following:

SELECT V.VendorNum, Sum(P.InvoiceAmt) as InvAmount
FROM Payments as P INNER JOIN Vendors as V
ON P.VendorNum = V.VendorNum
GROUP BY V.VendorNum
HAVING Count(Distinct V.VendState) > 1;

Any help would be appreciated.

 

[r937]

ms access doesn't support COUNT(DISTINCT ...)

how can a vendor have two different addresses in the vendor table? this would imply that the same vendor number is in there more than once

r937.com | rudy.ca

 

[eerich]

Is there another way to write the Count Distinct in Access?

Unfortunately the table does allow duplicate vendor numbers, thus the reason there is multiple addresses per vendor.

 

[SoCalAccessPro]

When you group by, you have to include every field that you are displaying in the group by clause.

Drop the amount and just count the duplicates in the Vendor table.

You can use this query as a sub query if you need the amounts ir States they are in.

Select VendorNum
From Vendors
Group By VendorNum
HAVING Count(VendorNum) > 1;


Also, why not just use the GUI to create your query ?



Tyrone Lumley
SoCalAccessPro

http://www.socalaccesspro.com

 

[r937]

Select VendorNum
From Vendors
Group By VendorNum
HAVING Count(VendorNum) > 1;

but that was what i had trouble understanding... how can two different vendors have the same vendor number? or, alternatively, how could the same vendor be in the vendor table more than once with the same vendor number?


something fishy goin' on...


r937.com | rudy.ca

 

[Golom]

something fishy goin' on...

... unless of course, VendorNum is not the key field in the table. In which case there's

something [red]really[/red] fishy goin' on...

 

[SoCalAccessPro]

VendorNum + State could be the PK.....

Tyrone Lumley
SoCalAccessPro

http://www.socalaccesspro.com

 

[r937]

VendorNum + State could be the PK.....

in that case, calling the table Vendors is egregiously misleading




r937.com | rudy.ca