Thread: Access 2000

  1. #1
    Registered User CumQuaT's Avatar
    Join Date
    Sep 2001
    Posts
    73

    Question 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!
    Last edited by CumQuaT; 07-31-2003 at 05:53 PM.
    Why? The often unanswerable question. If it is unanswerable, why answer it?

    Join the Cult of Sheograth, it's the place to be!

    http://cultosheogorath.proboards16.com

    Lord, we know what we are, yet we know not what we may be... Who wrote that anyway? If you know, E-Mail me. [email protected]

    Joy to all the fishes...

    **infected by frenchfry164**

  2. #2
    End Of Line Hammer's Avatar
    Join Date
    Apr 2002
    Posts
    6,231
    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
    When all else fails, read the instructions.
    If you're posting code, use code tags: [code] /* insert code here */ [/code]

  3. #3
    Registered User CumQuaT's Avatar
    Join Date
    Sep 2001
    Posts
    73

    reply

    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!
    Why? The often unanswerable question. If it is unanswerable, why answer it?

    Join the Cult of Sheograth, it's the place to be!

    http://cultosheogorath.proboards16.com

    Lord, we know what we are, yet we know not what we may be... Who wrote that anyway? If you know, E-Mail me. [email protected]

    Joy to all the fishes...

    **infected by frenchfry164**

  4. #4
    End Of Line Hammer's Avatar
    Join Date
    Apr 2002
    Posts
    6,231
    Yeah, I kinda mis-read what you wanted

    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.
    When all else fails, read the instructions.
    If you're posting code, use code tags: [code] /* insert code here */ [/code]

Popular pages Recent additions subscribe to a feed

Similar Threads

  1. Compiler that uses dos.h
    By teknologik in forum C Programming
    Replies: 8
    Last Post: 05-12-2007, 01:17 PM
  2. Replies: 3
    Last Post: 09-22-2003, 09:48 PM
  3. msvc++ 6 and ms access 2000
    By dune911 in forum Windows Programming
    Replies: 4
    Last Post: 08-19-2002, 06:57 AM
  4. using DAO without MS Access installed
    By zMan in forum Windows Programming
    Replies: 2
    Last Post: 02-20-2002, 03:14 PM