Results 1 to 4 of 4
  1. #1
    Dee300 is offline Advanced Intermediate
    Windows XP Access 2007
    Join Date
    Mar 2011
    Posts
    12

    sort query by two fields, populate one


    I have a combo box based on a query where I want the user to see two fields:

    1. Department field - sorted ascending
    2. Business process field - sorted ascending within the department field.

    However, I do not want the department field to populate, only the business process. If I reverse the columns in the query, then it populates the business process, but the sorting is not as I want.

    Any suggestions are appreciated.

  2. #2
    TheShabz is offline Court Jester
    Windows XP Access 2003
    Join Date
    Feb 2010
    Posts
    1,368
    set the column width of the department field to 0"

  3. #3
    ssanfu is offline Master of Nothing
    Windows 2K Access 2000
    Join Date
    Sep 2010
    Location
    Anchorage, Alaska, USA
    Posts
    9,664
    Let's say you have tables named:

    tblDepartment with fields
    Dept_PK (AN)
    DeptDesc (Text)


    tblBusinesss with fields
    Business_PK (AN)
    Dept_FK (Long)
    BusProcessDesc (Text)

    Related 1 to many on
    Dept_PK -> Dept_FK
    So you make a query like this:

    SELECT tblDepartments.Dept_PK, tblDepartments.DeptDesc, tblBusinesss.BusProcess_Desc
    FROM tblDepartments INNER JOIN tblBusinesss ON tblDepartments.Dept_PK = tblBusinesss.Dept_FK
    ORDER BY tblDepartments.DeptDesc, tblBusinesss.BusProcessDesc;
    One of the properties of a combo box is the "Bound Column". But Dept_PK is the bound column in this example and you want to store the PK of the business process.


    Easy enough. Change the query to :

    SELECT tblBusinesss.Business_PK, tblDepartments.DeptDesc, tblBusinesss.BusProcessDesc
    FROM tblDepartments INNER JOIN tblBusinesss ON tblDepartments.Dept_PK = tblBusinesss.Dept_FK
    ORDER BY tblDepartments.DeptDesc, tblBusinesss.BusProcessDesc;
    Note that the first column (the bound column) is now the PK of the business process and that it (if bound to a field) will be saved.

    Of course, you would set the column widths to 0;1;1 if you didn't want to display the first column (the PK).

    Another option is to change the bound column property from 1 the the number of the column that you want to save. If the PK column was the third column, in the dialog form, change the bound column from 1 to 3.

  4. #4
    Dee300 is offline Advanced Intermediate
    Windows XP Access 2007
    Join Date
    Mar 2011
    Posts
    12

    Smile

    SSANFU,

    Thank you that works exactly how I wanted it to!

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Auto Populate two fields
    By funkygoorilla in forum Access
    Replies: 7
    Last Post: 08-31-2011, 09:06 PM
  2. Replies: 3
    Last Post: 10-06-2010, 06:33 AM
  3. Query and populate fields
    By isnpms in forum Access
    Replies: 9
    Last Post: 08-28-2010, 08:31 AM
  4. auto populate mutiple fields
    By jomoan58 in forum Access
    Replies: 1
    Last Post: 07-23-2010, 01:03 PM
  5. Auto populate fields on a form
    By ldarley in forum Forms
    Replies: 0
    Last Post: 08-14-2008, 09:39 AM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums