[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

***********************************************************************
The information transmitted is intended solely for the individual or entity
to which it is addressed and may contain confidential and/or privileged
material. Any review, retransmission, dissemination or other use of or
taking action in reliance upon this information by persons or entities other
than the intended recipient is prohibited. If you have received this email
in error please contact the sender and delete the material from any
computer. As a recipient of this email, you are responsible for screening
its contents and the contents of any attachments for the presence of
viruses. No liability is accepted for any damages caused by any virus
transmitted by this email.
-- 
AccessD mailing list
AccessD at databaseadvisors.com
http://databaseadvisors.com/mailman/listinfo/accessd
Website: http://www.databaseadvisors.com