SQL count distinct(?) in Access97 newbie question

[thurman]

2 Questions:

1) When trying to count the total number of departments in our
organization,
How does one code a SQL statement like (and why doesnt this SQL work
in Access97?):

SELECT Count(DISTINCT dept)
FROM employees;

so that a employee table like:

Employee# Dept
Jones Accounting
Smith Sales
Kafka Sales
Rogers Research

will return 3( for the total count of departments in our company)?

I keep getting a "issing operator error"or a "ill only return 1
record"error. The code seems like it is ANSI92 compliant (I think) .
WHat gives?

2) Is there any way to comment on SQL code in Access97 (Itried C++, C
style etc... to no avail)

[Trysql]

Greetings,

SELECT Count(DISTINCT fieldname) is not supported in Access/jet. For most
current single query sql solution see post:

Subject: FYI on count distinct, once again
From: try...@aol.com (Trysql)
Newsgroups: comp.databases.ms-access
Date: 11 Feb 1999

Where yourfield would be dept.

Note that solution can be extended to the case where there are grouping
field(s).
General solution where yourfield occurs within a group (grp) is:

SELECT a.grp,Count(*) AS distinct_cnt_within_grp
FROM yourtable AS a LEFT JOIN yourtable AS b
ON b.grp=a.grp AND b.yourfield=a.yourfield AND b.key<a.key
WHERE b.grp Is Null
GROUP BY a.grp
ORDER BY 1;

Comments are not allowed in sql queries constructed in sql design/sql view.You
can comment them as part of vba.

Steve Dassin
Independent Database Consultant

>rr2...@earthlink.net (thurman)writes:

[Gary Labowitz]

You could do something like
set rs = db.openrecordset("TRANSFORM Count(Employees.Dept) AS CountOfDept
SELECT Employees.Dept
FROM Employees
GROUP BY Employees.Dept
PIVOT Employees.Dept;")
This is a crosstab query. It will give you the number of departments (the
number of rows returned) as well as the number of employees in each
department (the column values of each department). This might be more
useful.

Not sure of your problem on number 2. The single quote is the comment
character.
--
Gary (MCT, MCPS, MCSD)
http://www.enter.net/~garyl/ for references to good books
Contribute to ga...@enter.net
ICQ 6375624
thurman wrote in message <36cf3349...@news.earthlink.net>...

[Arvin Meyer]

On question 2: There is no way to comment SQL code in a SQL window. You can
use the description property of the query to write a short comment, or as I
sometimes do during development, build a table with the name, sql, and
comment so you can document what you are doing.
-----
Arvin Meyer
ons...@esinet.net

Gary Labowitz wrote in message <36cfd...@news3.enter.net>...

[Doug Hutcheson]

Try:
SELECT Count(SELECT DISTINCT em.dept FROM employees em)
FROM employees

Warning:air(head) code - may not work

Cheers,
Doug