[AccessD] Sorting and restricting ComboBox on ListBox contents

Thanks, Drew and Gustav for responding.
I tried Max, but sometimes the ID I need is in the middle of the IDs.
I didn't think about using Top, I'll try that.

thanks again.
Jim 



-----Original Message-----
From: accessd-bounces at databaseadvisors.com
[mailto:accessd-bounces at databaseadvisors.com]On Behalf Of Gustav Brock
Sent: Friday, April 16, 2004 4:42 AM
To: Access Developers discussion and problem solving
Subject: Re: [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

-- 
_______________________________________________
AccessD mailing list
AccessD at databaseadvisors.com
http://databaseadvisors.com/mailman/listinfo/accessd
Website: http://www.databaseadvisors.com