Query Help

[ebarrera]

I am looking to get a main instructional method for a each course that exists in a query. I query a table that has all the assignments which gives me duplicates. I grouped them to get a short list with a few duplicates if the course has two instructional methods (i.e. Lecture & Lab).

[COURSE] / [Method]
CIS-101-01 / Lecture
CIS-101-01 / Lab
CIS-151-501 / Lecture
CIS-100L-01 / Lab

I need to breakdown the list to one course and one method type for finals pay. Any courses that have just lecture are lecture and any courses that have lab are lab...simple. The hardest one I can't figure is the courses that have lecture and lab. I need a return of lecture as the instructional method for finals week pay called [Finals Method]. In excel I was able to sort and manually assign the method by looking and a field I created that searches for duplicates...if true, lecture, [Method]. Any help is greatly appreciated.

[COURSE] / [Finals Method]
CIS-101-01 / Lecture
CIS-151-501 / Lecture
CIS-100L-01 / Lab

 

[ebarrera]

Firgured a way of getting what i wanted...created a table with method orders, Lec = 1, Lab =2. Then did a query to list method orders. Grouped them and got [FirstOfMethod Order]. Did another query and where I only have the course and a field called [Finals Method], where I convert [FirstOfMethod Order] to "Lec" or "Lab. Probably not the best way, but it works.

 

[ByteMyzer]

Try the follow SQL statement (substitute highlighted text with actual table/field names):

SELECT T1.[b][i]COURSE[/i][/b], MAX(T1.[b][i]METHOD[/i][/b]) AS [Finals Method]
FROM [b][i]MyTable[/i][/b] AS T1
GROUP BY T1.[b][i]COURSE[/i][/b];

 

[ebarrera]

Thanks ByteMyzer, will try and let you know if I get the same result...always better to complete in one query versus three.