[kicker22]

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

[Pat Phelan]

DISTINCT

-PatP

[kicker22]

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

[Pat Phelan]

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!

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

[kicker22]

it worked.

thanks

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