Qry to return 1st instance of item in a list, vs. any instances beyond 1st instance

[Lynn Cohen]

I'm working in Access 2010; not a total novice, but not an experienced programmer either.

I have a list of professors, each of whom has a list of course numbers associated with him/her. Here is a part of the list, the courses associated with Prof A and Prof B:
Prof A BIO 101-01
Prof A BIO 101-02
Prof A BIO 111-03
Prof A CHM 090-01
Prof A CHM 095-06
Prof A CHM 095-08
Prof A CHM 101-05
Prof B ELT 105-01
Prof B ELT 105-02
Prof B ELT 123-01
Prof B ELT 225-02

The part before the hyphen is the “course number” field and the part after the hyphen is the “section number” field. Both fields are formatted as text fields and that can’t change.



My whole list has 125+ professors and 500+ courses.

For my whole list I need to write a query that returns a list of the first instance of each combination of “course number” plus “section number” for each professor. For each professor, the first instance is the lowest “section number” for any “course number.” In this example, that would be
Prof A BIO 101-01
Prof A BIO 111-03
Prof A CHM 090-01
Prof A CHM 095-06
Prof A CHM 101-05
Prof B ELT 105-01
Prof B ELT 123-01
Prof B ELT 225-02

I also need a separate query that returns any instance of a combination of “course number” plus “section number” beyond the first instance. For each professor, beyond the first instance is anything other than the lowest “section number” for any “course number.” In this case that would be
Prof A BIO 101-02
Prof A CHM 095-08
Prof B ELT 105-02

Can someone assist with the query verbiage I would need for these two lists?

Thanks very much.

Lynn

[June7]

Using TOP N parameter should be able to accomplish.

Review this recent thread: https://www.accessforums.net/reports...tax-38123.html

[Lynn Cohen]

Moderator,

Thanks for your quick reply. I looked at the thread for which you provided a link, and it doesn't seem to apply, but as I said, I'm not a real whiz at this.

I also tried creating a calculated field, [CourseSum], which is formatted as [course number] +"-"+[section number] and applied the MIN parameter to that. That returned a list of the first instance as derived from the entire [CourseSum] list, not the first instance as it applies to each individual professor.

I can express what I need as - tell the query "look at each professor and give me the lowest [CourseSec] number for each course associated with that professor"- just can't figure out how to write the parameter(s).

Thanks again.

[Dal Jeanis]

Assuming that the table is called MyTable, the professor name is ProfName, the full course section is called CourseSection, and the "BIO 101" portion of the coursesection is always 7 long:

Query0:
SELECT
   T0.ProfName,
   LEFT(T0.CourseSection,7) AS CourseNumber,
   MIN(T0.CourseSection) AS LowCourseSection
FROM
   MyTableName AS T0
GROUP BY 
   T0.ProfName,
   LEFT(T0.CourseSection,7);

Query1:
SELECT 
   Q0.ProfName, 
   Q0.LowCourseSection 
FROM Query0 AS Q0
ORDER BY 
   Q0.ProfName, 
   Q0.LowCourseSection;

Query2:
SELECT
   T2.ProfName,
   T2.CourseSection
FROM
   MyTableName AS T2
WHERE T2.CourseSection NOT IN
   (SELECT Q0.LowCourseSection
    FROM Query0 AS Q0
    WHERE Q0.ProfName = T2.ProfName)
ORDER BY 
   T2.ProfName, 
   T2.CourseSection;

[Lynn Cohen]

Thanks for your replies.

I was able to get the first instance of a combination of “course number” plus “section number” by:
creating a calculated field, CourseSec, which is defined as course number + section number
then using the MIN parameter on that field, in a query.

Then I built a second query with the parameter is null for the field MinOfCourseSec.

Dal, I want to especially thank you. I'm not a programmer (not even at the level of "open the SQL View and put some code there") and wasn't able to actually use what you suggested, but studying it gave me the direction I needed.

Lynn

[Dal Jeanis]

Happy to oblige.

The most important technique is this: solve the problem with a method you understand, because you're the one that needs to fix it when it breaks.

In this case, my code in post 4 assumed that course-section was already merged together, and it wasn't. Which means that the real code would have been simpler - you don't need LEFT to pull Coursenumber out, since it was already out.

For future reference, here's the instructions for "open the SQL View and put some code there"
1) Open the query in Design View. (For instance, in the nav pane, right-click the query and select "Design View".)
2) On the ribbon on the Design tab, the far left group is called Results. Click the Down arrow and select SQL View.