Access 2000

In Access 2000, I have a database with a table in it called STUDENTS. In STUDENTS, there is a column called CLASS. In the CLASS column, there are cells containing either 'A', 'B' or 'C'.
I need to program an SQL query to return the number 3 (There are three different data values in the column CLASS)
This is the code I entered:

SELECT Count(DISTINCT Class)
FROM Students;

I got the following error message:

Syntax error. Missing operator in query.

I've tried a few alternatives, but nothing seems to work! Have microsoft changed SQL between Access 2000 and Acess '98? And if so, how can I get this thing to work?


Here's an example of the table...

CLASS
A
A
B
B
C

It should result in a number 3. There being 3 different classes, A, B and C.

Thanx guys!

Code:

---

SELECT OrderNum, Sum(NumOrdered)  AS Total

FROM OrderLine

Group by OrderNum;

---

From here .

That's how I'd do it anyway, making yours like this:

Code:

---

SELECT Class, Count(*) As ClassCount
FROM Students
GROUP BY Class;

---

More details also here

That returns the number of each different class in the column, not how many different classes there are. The GROUP statement might have something to do with is though....... Very interesting...... I will have a fiddle..... Any more ideas though would be hot!

Yeah, I kinda mis-read what you wanted :rolleyes:

Anyways, from what I can find, your original query looks correct, but I guess if it isn't supported by Acc2000, you'll have to find another way.

The group won't help directly, but doing something like this may:

Code:

---

SELECT DISTINCT field INTO temp_table FROM mytable;
SELECT count(*) FROM temp_table;
DROP TABLE temp_table;

---

I "stole" the idea from here which stemed from here .

I don't have Acc2000 here, so you're on your own for now. :)