Results 1 to 3 of 3
  1. #1
    Star Lounger
    Join Date
    Jun 2002
    Location
    Georgia, U. S. A.
    Posts
    90
    Thanks
    2
    Thanked 0 Times in 0 Posts

    SELECT Statement (Access 2003)

    As part of the security for a testing application I've written in ASP, I require faculty to go to a web page and "OK" a class to take a test. The ASP page then checks to see if a teacher has done this before it will allow students to see the test. It actually gives the students 30 minutes to begin the test or the teacher will have to go back and "OK" the class again. If a student is sick and takes the test the next day or sometime later, there will end up being several instances or entries in the table that say something like Comp_Apps_Final_1, 1st Period, Mr. Zinn. I want to make a SELECT statement for reporting purposes that will populate a combo box with DISTINCT combinations like that above. In other words, there are multiple entries in the table but I only want to display one. That way the combo box will have only a few entries and will not confuse faculty in wondering which one they should click on. If this doesn't make sense, I'll try to clarify next time. Can anyone help me with a SELECT statement. It would look something like "SELECT DISTINCT [Test] And DISTINCT [Class] And DISTINCT [Teacher] FROM Security". Thanks for any help anyone can provide

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: SELECT Statement (Access 2003)

    The DISTINCT keyword should only be used once, immediately after SELECT. It will cause the SELECT statement to return unique combinations of the specified fields. That is, each field viewed separately may well contain duplicates, but there will be no repetition of the same combination of the fields. The SQL statement would look like this:

    SELECT DISTINCT Test, Class, Teacher FROM Security

    A good way to generate SQL statements is to create a query in design view, tweak it until it does what you want and then look at the SQL statement (View | SQL). In general, Access will generate rather 'verbose' SQL, but that doesn't really matter. In this example, you'd:
    - Create a query based on the Security table.
    - Add the Test, Class and Teacher fields to the query grid.
    - Click in an empty part of the upper half of the query design window.
    - Activate the Properties window.
    - Set the Unique Values property of the query to Yes (this corresponds to SELECT DISTINCT).
    - Select View | Datasheet to check that the query does what you want.
    - Select View | SQL to view the SQL statement.

  3. #3
    Star Lounger
    Join Date
    Jun 2002
    Location
    Georgia, U. S. A.
    Posts
    90
    Thanks
    2
    Thanked 0 Times in 0 Posts

    Re: SELECT Statement (Access 2003)

    Thanks Hans, As usual, you have the answers. I'm glad I found this site and your wealth of information.
    J. P. Zinn

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •