Query - Count

[SAC]

New help with count by in a query.

I have a Bill Of Lading items Table. A BOL can have several lines and
different vendors can be on different line or the same vendor can be on
different lines.

I'm trying to write a query that will count the number of vendors on a BOL.

I have:

SELECT BOLITMS.BOLNO, Count(BOLITMS.VENDNO) As CountOfVendNo
FROM BOLITMS
GROUP BY BOLITMS.BOLNO

I'm getting > 1 when there is only a single vendor for the bolno.

Any ideas?

Thanks.

 

[Douglas J. Steele]

Access doesn't allow you to count the values of a particular field like
that.

Create an intermediary query that returns only unique combinations of BOLNO
and VENDNO:

SELECT DISTINCT BOLNO, VENDNO
FROM BOLITMS

Base your count query on that query.

Assuming you're using Access 2000 or newer, you can do it all in one query:

SELECT BOLNO, Count(VENDNO) As CountOfVendNo
FROM
(
SELECT DISTINCT BOLNO, VENDNO
FROM BOLITMS
) AS Subquery
GROUP BY BOLNO

 

[SAC]

Excellent, Doug! Thanks.

Access doesn't allow you to count the values of a particular field like
that.

Create an intermediary query that returns only unique combinations of BOLNO
and VENDNO:

SELECT DISTINCT BOLNO, VENDNO
FROM BOLITMS

Base your count query on that query.

Assuming you're using Access 2000 or newer, you can do it all in one query:

SELECT BOLNO, Count(VENDNO) As CountOfVendNo
FROM
(
SELECT DISTINCT BOLNO, VENDNO
FROM BOLITMS
) AS Subquery
GROUP BY BOLNO