Results 1 to 5 of 5
  1. #1
    Join Date
    Apr 2004
    Location
    Michigan
    Posts
    44

    Unanswered: Need help with this query

    I really need help with this,
    this is the scenario, i am trying to list all students with SID, lname, fname, year term, program name

    Now here is the trick part, students can go to the same program more than one year..so when I run the query, it will list for me the student twice..

    Now I want to create a query that wont list the student twice who attended the same program twice. AND I still want it to list student twice or more if he attended different program or so..
    so yeah, what is the keyword in the query that will let me only select the students who went abroad with no year and programname match!

    thanks,
    hope to hear from you guys soon..

    morad

  2. #2
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    DISTINCT

    -PatP

  3. #3
    Join Date
    Apr 2004
    Location
    Michigan
    Posts
    44

    Wink hmm

    Well, I tried distinct before, and distinct only works when all fields match.
    but then all fields match except the term.

    For example.

    ID, SID, Name, Program_Name, Term
    01, 123, Ray , CEA, , 2004
    02, 123, Ray , CEA, , 2005


    Now when I run distinct, it will show both of them because term.value is different!

    so is there a way to run a query that will not list both of the records if the program_name are the same.?


    Let me purify what I want

    Problem 1:
    ID, SID, Name, Program_Name, Term
    01, 123, Ray , CEA, , 2004
    02, 123, Ray , CEA, , 2005

    Result wanted when run query:
    ID, SID, Name, Program_Name, Term
    02, 123, Ray , CEA, , 2005
    *it would show the most recent term record and not both of them, because program name match!

    Problem 2:
    ID, SID, Name, Program_Name, Term
    01, 123, Ray , CEA, , 2004
    02, 123, Ray , IIS, , 2005

    Result wanted when run a query:
    ID, SID, Name, Program_Name, Term
    01, 123, Ray , CEA, , 2004
    02, 123, Ray , IIS, , 2005
    *It would show both records because program name dont match.


    You are my life saver pat

    thanks

  4. #4
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    Let's try this one out to see how it flies... It might not be exactly what you want, but I'll bet it is at least pretty close!
    Code:
    SELECT Max(a.id), a.SID, a.Name, a.Program_name, Max(a.Year)
       FROM myTable AS a
       GROUP BY a.SID, a.Name, a.Program_name
    -PatP

  5. #5
    Join Date
    Apr 2004
    Location
    Michigan
    Posts
    44

    la la la

    it worked.

    thanks

    p.s. I learned a lot from you..

Posting Permissions

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