[AccessD] SQL count distinct problem

Hi Jim

Simple? Normally you are not that scared?
Just use Lambert's SQL as a subquery:

  SELECT
    Vendor,
    Count(CHKNO) As CountChecks
  FROM
    (SELECT DISTINCT Table1.Vendor, Table1.CHKNO FROM Table1)
  GROUP BY
    Vendor;

But checks? I can't believe this. We are writing 2006 and you are still mailing checks around by surface mail at extensive costs?
Here checks are only used for very special occasions. Every normal kind of B2B payment is electronic. Imagine what level of potential cost savings you are facing ...

/gustav

>>> Jim.Hale at FleetPride.com 07-03-2006 23:26 >>>
That will certainly work but I was kind of hoping I was missing something
simple (Like the distinct keyword) that would allow me to do it in one
query.
Jim Hale

-----Original Message-----
From: Heenan, Lambert [mailto:Lambert.Heenan at aig.com] 
Sent: Tuesday, March 07, 2006 4:12 PM
To: 'Access Developers discussion and problem solving'
Subject: Re: [AccessD] SQL count distinct problem


You'll need a query that looks like this...

SELECT DISTINCT Table1.Vendor, Table1.CHKNO FROM Table1;

Then you can use that query as the source of your other query to do the
grouping and counting.

Lambert

-----Original Message-----
From: accessd-bounces at databaseadvisors.com 
[mailto:accessd-bounces at databaseadvisors.com] On Behalf Of Hale, Jim
Sent: Tuesday, March 07, 2006 4:53 PM
To: 'Accessd (E-mail)
Subject: [AccessD] SQL count distinct problem


Assume a table with vendor, inv amt and check number. There may be multiple
records for the vendor with the same check number. If I want to determine
the total amt paid to a vendor and the number of checks written to him a
simple groupby query will not do since the count function will count the
number of occurrences not number of distinct checks. COUNT(DISTINCT(CHKNO)
works in a pass through query on the AS400 but not in Jet SQL. What is the
equivalent Jet SQL to determine the number of distinct checks written per
vendor? TIA Jim Hale