Results 1 to 4 of 4

Thread: Query Help

  1. #1
    Join Date
    Aug 2003
    Posts
    31

    Unanswered: Query Help

    Hi all i have a problem with a query.

    I have this information in a table

    School------------Teacher-------------Date

    A------------------Matt-----------------09/10/03
    A------------------Matt-----------------15/10/03
    A------------------John-----------------22/10/03
    B------------------John-----------------02/10/03

    What i would like to do, is make a select query which will show me the earliest date for each school and the teacher visiting.

    So from my example the query should show

    School-----------Teacher--------------Date

    A-----------------Matt------------------9/10/03
    B-----------------John------------------2/10/03

    Im sure this is really easy but i cant seem to do it.

    Thanks for your help in advance.

    Matt

  2. #2
    Join Date
    Aug 2003
    Posts
    31

    Query help

    Hi All,

    I have posted a copy of the database. As you can see in the db the query i have made shows 2 examples for the school "abbott" whereas i only want it to show the earliest date.

    Thanks again

    Matt
    Attached Files Attached Files

  3. #3
    Join Date
    Feb 2003
    Location
    Winston-Salem,NC
    Posts
    34

    Wink try this

    I made a mock database with the information you had listed and tried this and I got the results you had posted. Open a new query and change to SQL view, plug this in and change the name of the table (Teacher in my case) to whatever the name of the table is you are getting this information from and it should work.

    SELECT DISTINCT Teacher.School, First(Teacher.Teacher) AS Teacher, First(Teacher.Date) AS [Date]
    FROM Teacher
    GROUP BY Teacher.School;

    Hope this helps.
    B

  4. #4
    Join Date
    Feb 2003
    Location
    Winston-Salem,NC
    Posts
    34

    same thing ur db

    I posted my last reply before I saw ur sample db, I modified the code like it should be for you sample db.


    SELECT DISTINCT School.School, First(School.Teacher) AS Teacher, First(School.Date) AS [Date]
    FROM School
    GROUP BY School.School;

    hope this helps
    B

Posting Permissions

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