Change Sorting and Grouping Sequence

[Jason]

Hi,

There is a report showing tasks assigned to employee. However, some of the
tasks are not assigned yet AND we want to display it as "Not Assigned".

The Employee Name Field is already Grouped and Sorted in ascending order,
would it be possible to display as follow:

Albert
Task 1
Task 3

Mary
Task 5

Zoë
Task 6

Not Assigned
Task 2
Task 4

instead of

Albert
Task 1
Task 3

Mary
Task 5

Not Assigned
Task 2
Task 4

Zoë
Task 6

Thank you for your help.

 

[Douglas J. Steele]

Add a computed field to the underlying query, and have it return 1 if the
Employee Name field is "Not Assigned" or 0 otherwise. Add that computed
field to the Sorting fields immediately above Employee Name.

 

[Jason]

Dear Douglas,

Thank you for your suggestion. I have thought of adding another field for
sorting as well.

On the other hand, I don't know what is the meaning of "Computed Field".
Would it be possible to give me some enlightenment?

Thanks
Jason

 

[Douglas J. Steele]

You can add fields to queries that include calculations.

Now, I don't know whether you're actually storing "Not Assigned" in the
employee name field, or if you're putting it there based on other
conditions.

If you are storing "Not Assigned", try typing something like the following
into an empty cell on the Field row of the query:

SortField: IIf([EmployeeNm] = "Not Assigned", 1, 0)

If "Not Assigned" is based on other conditions, use those conditions as the
boolean condition in the Iif statement.

 

[Jason]

Dear Doughas,

If there is no employee assigned, it just gets NULL value. Is it a good idea
to give a default value of 7 (I have already added 6 employee in the
Employee Table) for the EmployeeID field in the Project Table ?

If we do so, how can I exclude "Not Assigned" in the Employee Form ? It is
not desirable to allow end users finding that ID = 7 is named "Not
Assigned".

On the other hand, thank you for advising me to use IIF statement to get the
computed field.

Thanks
Jason

You can add fields to queries that include calculations.

Now, I don't know whether you're actually storing "Not Assigned" in the
employee name field, or if you're putting it there based on other
conditions.

If you are storing "Not Assigned", try typing something like the following
into an empty cell on the Field row of the query:

SortField: IIf([EmployeeNm] = "Not Assigned", 1, 0)

If "Not Assigned" is based on other conditions, use those conditions as
the boolean condition in the Iif statement.

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)

Dear Douglas,

Thank you for your suggestion. I have thought of adding another field
for sorting as well.

On the other hand, I don't know what is the meaning of "Computed Field".
Would it be possible to give me some enlightenment?

Thanks
Jason

 

[Douglas J. Steele]

Having a Null value is fine (not, I wouldn't add an "Not Assigned" value to
your Employee table)

Your computed field would be:

SortField: IIf(IsNull([EmployeeNm]), 1, 0)


--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)

Dear Doughas,

If there is no employee assigned, it just gets NULL value. Is it a good
idea to give a default value of 7 (I have already added 6 employee in the
Employee Table) for the EmployeeID field in the Project Table ?

If we do so, how can I exclude "Not Assigned" in the Employee Form ? It
is not desirable to allow end users finding that ID = 7 is named "Not
Assigned".

On the other hand, thank you for advising me to use IIF statement to get
the computed field.

Thanks
Jason

You can add fields to queries that include calculations.

Now, I don't know whether you're actually storing "Not Assigned" in the
employee name field, or if you're putting it there based on other
conditions.

If you are storing "Not Assigned", try typing something like the
following into an empty cell on the Field row of the query:

SortField: IIf([EmployeeNm] = "Not Assigned", 1, 0)

If "Not Assigned" is based on other conditions, use those conditions as
the boolean condition in the Iif statement.

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)

Dear Douglas,

Thank you for your suggestion. I have thought of adding another field
for sorting as well.

On the other hand, I don't know what is the meaning of "Computed Field".
Would it be possible to give me some enlightenment?

Thanks
Jason

Add a computed field to the underlying query, and have it return 1 if
the Employee Name field is "Not Assigned" or 0 otherwise. Add that
computed field to the Sorting fields immediately above Employee Name.

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)



Hi,

There is a report showing tasks assigned to employee. However, some
of the tasks are not assigned yet AND we want to display it as "Not
Assigned".

The Employee Name Field is already Grouped and Sorted in ascending
order, would it be possible to display as follow:

Albert
Task 1
Task 3

Mary
Task 5

Zoë
Task 6

Not Assigned
Task 2
Task 4

instead of

Albert
Task 1
Task 3

Mary
Task 5

Not Assigned
Task 2
Task 4

Zoë
Task 6

Thank you for your help.