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)
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...
It should result in a number 3. There being 3 different classes, A, B and C.
From here .
SELECT OrderNum, Sum(NumOrdered) AS Total
Group by OrderNum;
That's how I'd do it anyway, making yours like this:
More details also here
SELECT Class, Count(*) As ClassCount
GROUP BY Class;
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:
I "stole" the idea from here which stemed from here .
SELECT DISTINCT field INTO temp_table FROM mytable;
SELECT count(*) FROM temp_table;
DROP TABLE temp_table;
I don't have Acc2000 here, so you're on your own for now. :)