sort query by two fields, populate one

[Dee300]

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.

[TheShabz]

set the column width of the department field to 0"

[ssanfu]

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.

[Dee300]

SSANFU,

Thank you that works exactly how I wanted it to!