Distinct Count

[Guest]

I am currently creating a query of payables data based on payment term
information. I would like the Vendor ID count to be distinct. When I insert
a Count(Distinct zapv.pymt.vendorid) I get a syntax error.

SELECT ZAPV_PYMT.[Payment Terms ID], Count(ZAPV_PYMT.VendorID) AS
CountOfVendorID, Count(ZAPV_PYMT.VoucherID) AS CountOfVoucherID,
ZAPV_PYMT.[Transaction Currency], Sum(ZAPV_PYMT.[Gross Invoice Amount]) AS
[SumOfGross Invoice Amount]
FROM ZAPV_PYMT
WHERE (((ZAPV_PYMT.[GL Business Unit])="A0041"))
GROUP BY ZAPV_PYMT.[Payment Terms ID], ZAPV_PYMT.[Transaction Currency];

Thank You,
Paul

 

[Guest]

Couple of things
1. Change the Where with Having
2. Change the order between the Having and and the Group By

Try:

SELECT ZAPV_PYMT.[Payment Terms ID], Count(ZAPV_PYMT.VendorID) AS
CountOfVendorID, Count(ZAPV_PYMT.VoucherID) AS CountOfVoucherID,
ZAPV_PYMT.[Transaction Currency], Sum(ZAPV_PYMT.[Gross Invoice Amount]) AS
[SumOfGross Invoice Amount]
FROM ZAPV_PYMT
GROUP BY ZAPV_PYMT.[Payment Terms ID], ZAPV_PYMT.[Transaction Currency];
HAVING (((ZAPV_PYMT.[GL Business Unit])="A0041"))

 

[Guest]

Ofer,

Thank you for the suggestion but I received a error that the ZAPV_PYMT.[GL
Business Unit] was not part of the Aggregate Function. Any suggestions?

Thanks,
Paul

Couple of things
1. Change the Where with Having
2. Change the order between the Having and and the Group By

Try:

SELECT ZAPV_PYMT.[Payment Terms ID], Count(ZAPV_PYMT.VendorID) AS
CountOfVendorID, Count(ZAPV_PYMT.VoucherID) AS CountOfVoucherID,
ZAPV_PYMT.[Transaction Currency], Sum(ZAPV_PYMT.[Gross Invoice Amount]) AS
[SumOfGross Invoice Amount]
FROM ZAPV_PYMT
GROUP BY ZAPV_PYMT.[Payment Terms ID], ZAPV_PYMT.[Transaction Currency];
HAVING (((ZAPV_PYMT.[GL Business Unit])="A0041"))


--
Good Luck
BS"D

I am currently creating a query of payables data based on payment term
information. I would like the Vendor ID count to be distinct. When I insert
a Count(Distinct zapv.pymt.vendorid) I get a syntax error.

SELECT ZAPV_PYMT.[Payment Terms ID], Count(ZAPV_PYMT.VendorID) AS
CountOfVendorID, Count(ZAPV_PYMT.VoucherID) AS CountOfVoucherID,
ZAPV_PYMT.[Transaction Currency], Sum(ZAPV_PYMT.[Gross Invoice Amount]) AS
[SumOfGross Invoice Amount]
FROM ZAPV_PYMT
WHERE (((ZAPV_PYMT.[GL Business Unit])="A0041"))
GROUP BY ZAPV_PYMT.[Payment Terms ID], ZAPV_PYMT.[Transaction Currency];

Thank You,
Paul

 

[Guest]

Sorry its my mistake, your sintax was OK.

I didn't notice that the field you wanted to write
Count(Distinct zapv.pymt.vendorid)

has two dots in it, so I assume the name i wrong.
Also I'm don't thing you can do that, adding the Distinct to the count

What do you want to achieve by that?

 

[Guest]

My error on the name. Count(Distinct zapv_pymt.[vendorid]. I'm trying to
achieve a count of vendors and vouchers within the zapv_pymt table. The
scenario, I have one table that contains a one to many match of vendorids to
voucherids. I would like to count the number of vendors used and a separate
count of the vouchers processed by payment term id. I can achieve this by
building two or three queries and doing joins but I would rather create one
query using the Distinct feature.

Thanks,
Paul

 

[John Spencer]

Access SQL does not support Count Distinct. The only method I know of
is to use a subquery

SELECT Count(*) FROM (Select Distinct SomeField From SomeTable WHERE
SomeField = SomeValue) as D

'====================================================
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
'====================================================

My error on the name. Count(Distinct zapv_pymt.[vendorid]. I'm trying to
achieve a count of vendors and vouchers within the zapv_pymt table. The
scenario, I have one table that contains a one to many match of vendorids to
voucherids. I would like to count the number of vendors used and a separate
count of the vouchers processed by payment term id. I can achieve this by
building two or three queries and doing joins but I would rather create one
query using the Distinct feature.

Thanks,
Paul

Sorry its my mistake, your sintax was OK.

I didn't notice that the field you wanted to write
Count(Distinct zapv.pymt.vendorid)

has two dots in it, so I assume the name i wrong.
Also I'm don't thing you can do that, adding the Distinct to the count

What do you want to achieve by that?