Results 1 to 4 of 4
  1. #1
    Star Lounger
    Join Date
    Oct 2002
    Location
    Sheffield, Yorkshire, England
    Posts
    80
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Combo box row source (Access 2000)

    Loungers
    I would like some advice on what I am attempting is possible. I have a suppliers table that has 3,000+ records, thus making the combo box to select a supplier unwieldy. What I would like to have is the top 20 suppliers (calculated by counting the occurrence of suppliers in another table) and have these appear at the top of the list with the rest of the suppliers underneath them.

    I've tried a union query of the top 20 query and the rest of suppliers query but the result sorts the entries back into supplier number order.

    TIA
    Graham

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Combo box row source (Access 2000)

    Idea 1:

    Create queries like this:
    a) The top 20 query you already have ; add a column SortOrder: 1. Let's say this is qryTop20
    [img]/forums/images/smilies/cool.gif[/img] A query based on the suppliers table and the top 20 query. Create an outer join on the Supplier ID (display all records from the suppliers table). Add the fields from the suppliers table you need, plus the Supplier ID from the query with criteria Is Null, and an extra column SortOrder: 2. Call this qryRest.
    c) A union query

    SELECT * FROM qryTop20
    UNION SELECT * FROM qryRest
    ORDER BY SortOrder;

    <img src=/w3timages/blueline.gif width=33% height=2>

    Idea 2:

    Create a totals query returning the supplier info you need, plus the count of occurrences. Sort descending on this count. Use this query as row source. Seems much simpler than the above.

    <img src=/w3timages/blueline.gif width=33% height=2>

    Idea 3:

    Assign some kind of category to the suppliers. Let the user select a category from one combo box, then a supplier within that category from another combo box. Search for synchronize combo boxes or cascading combo boxes if you are interested.

  3. #3
    Plutonium Lounger
    Join Date
    Dec 2000
    Location
    Sacramento, California, USA
    Posts
    16,775
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Combo box row source (Access 2000)

    If you do this, your combobox performance will still be poor because of the number of rows. Plus, you will have disabled the alpha match on the supplier name, since it will always find the first one, which may or may not be in the top 20. You might be better off taking Hans suggestion about synchronized combos.
    Charlotte

  4. #4
    Star Lounger
    Join Date
    Oct 2002
    Location
    Sheffield, Yorkshire, England
    Posts
    80
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Combo box row source (Access 2000)

    Thanks for the ideas Hans. It's easy when pursuing an idea to lose sight of the obvious!

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •