[AccessD] Sorting and restricting ComboBox on ListBox contents

Hi Jim

> I have a list box with over 130 items all rows are distinct but a few columns are duplicates.  The ListBox is sorted on two text fields which puts the ID number out of sequence.  The ComboBox sorts
> and "Groups By" duplicate columns.  What I want to do is use the ComboBox to jump to the first instance of one of the duplicate columns.

> e.g.

> ID      CourseName      City
> 135     CCP             EC
> 90      DIS             EC
> 87      DIS             MP
> 18      DOC             CD
> 17      DOC             SR
> 55      DSC M           AL
> 57      DSC M           EC
> 43      DSC M           MP
> 28      DSC M           SF
> 3       DSC M           SR

> What I want is:
> 135     CCP             EC
> 90      DIS             EC
> 18      DOC             CD
> 55      DSC M           AL
> 3       DSC M           SR

> What I get is:
> 135     CCP             EC
> 87      DIS             MP
> 17      DOC             SR
> 3       DSC M           SR

> What needs to be done to what the desired results?

As Drew notes, you'll have to pick the sorted values for ID and City
for each CourseName.
Two subqueries can be used for this:

<SQL>

  SELECT
    (SELECT
        TOP 1 ID
      FROM
        tblCourses AS aliC
      WHERE
        aliC.CourseName = tblCourses.CourseName
      ORDER BY
        City,
        ID DESC;) AS TopID,
    CourseName,
    (SELECT
        TOP 1 City
      FROM
        tblCourses AS aliC
      WHERE
        aliC.CourseName = tblCourses.CourseName
      ORDER BY
        City,
        ID DESC;) AS TopCity
  FROM
    tblCourses
  GROUP BY
    CourseName;

</SQL>

Have fun!

/gustav