[AccessD] SQL count distinct problem

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

***********************************************************************
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
-- 
AccessD mailing list
AccessD at databaseadvisors.com
http://databaseadvisors.com/mailman/listinfo/accessd
Website: http://www.databaseadvisors.com

***********************************************************************
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.