Sorting and Grouping on an Expression in a Report

[Coolboy55]

I have a table called Employees/Skills that has the following fields: EmployeeID, SkillID, DateTrained. I have a report whose goal is to group and sort alphabetically by SkillDescriptio n (a field in the Skills table) and show the employees who have that skill and the date they were trained on that skill.

I created the expression following expression to sort and group by:

=DLookUp("SkillDescription","Skills","[Skills].[SkillID]=Reports![Test]![SkillID]")

It is not doing what I want it to do... what am I doing wrong?

Thanks!

CB55

[MichaelSL]

I ran into sorting problems also. Try this
1) Go to Design View for your report
2) Right-Click in the Report or Page Header section
3) Left-Click Sorting and Grouping
4) Setup your sorting here.

[Coolboy55]

I ran into sorting problems also. Try this
1) Go to Design View for your report
2) Right-Click in the Report or Page Header section
3) Left-Click Sorting and Grouping
4) Setup your sorting here.

I have done that, but the expression I'm using in the Sorting and Grouping is not sorting alphabetically the way I expect... :/

[MichaelSL]

I am sorry if you are not getting what you expect. If the sorting dialog box doesn't do it for you, I cannot help. Hopefully someone else can You might want to describe exactly what you put into that dialog box. Good Luck - MSL

[Coolboy55]

I am sorry if you are not getting what you expect. If the sorting dialog box doesn't do it for you, I cannot help. Hopefully someone else can You might want to describe exactly what you put into that dialog box. Good Luck - MSL

The code in my first post is what I put in the Field/Expression, and I also created an unbound textbox with the same code in the group header for this expression. Any experts out there know what I'm doing wrong? :(

[Stewart Ross]

Hi CB55. You can't sort a report by trying to group it on a lookup of a value that isn't in the recordset for the report. The disconnected Dlookup will be evaluated at most just once for each grouped section of your report, not on every record as you expect, so trying to sort on it is just not going to work.

As a general rule, don't base reports (or forms) directly on a base table; use a query instead. You have much more flexibility in what you include, and on what the sort order should be. You can also use joins between tables to provide views of the data not available when you use the underlying table itself as the base.

Create a new query and include both tables, Employees/Skills and Skills. Make sure they are joined on the correct field or fields, and include all necessary fields from both tables for your report (including the SkillDescriptio n field). Change the recordsource of your report to this new query, then sort on the SkillDescriptio n field in your report. It will save you an awful lot of effort.

The use of such joined-table queries as the source for forms and reports is really the routine way to go, and once you see what flexibility it gives you will be unlikely to go back to basing them on base tables.

-Stewart

[Coolboy55]

Hi CB55. You can't sort a report by trying to group it on a lookup of a value that isn't in the recordset for the report. The disconnected Dlookup will be evaluated at most just once for each grouped section of your report, not on every record as you expect, so trying to sort on it is just not going to work.

As a general rule, don't base reports (or forms) directly on a base table; use a query instead. You have much more flexibility in what you include, and on what the sort order should be. You can also use joins between tables to provide views of the data not available when you use the underlying table itself as the base.

Create a new query and include both tables, Employees/Skills and Skills. Make sure they are joined on the correct field or fields, and include all necessary fields from both tables for your report (including the SkillDescriptio n field). Change the recordsource of your report to this new query, then sort on the SkillDescriptio n field in your report. It will save you an awful lot of effort.

The use of such joined-table queries as the source for forms and reports is really the routine way to go, and once you see what flexibility it gives you will be unlikely to go back to basing them on base tables.

-Stewart

Thanks Stewart! Many of my reports are based on queries, but for some reason I don't think of it for simpler reports with only a few outputs. I will get into the habit of basing all reports on queries. But I don't understand what advantage there is in basing a form on a query except in the rare case, because to edit the table data, wouldn't you need to then use SQL to UPDATE and INSERT and DELETE instead of editing data in controls whose record source is the desired field?

Thanks,

CB55

[Stewart Ross]

Hi CB55. If relationships are correctly defined between tables then multi-table queries using INNER, LEFT or RIGHT joins are normally updatable, as long as the joined fields are taken from the correct side of the relationships. Not once have I had to use an SQL Update or Insert statement with any form, in the 16 years I have been designing with Access. I don't tend to use complex table joins - appended below is an excerpt from a Staff query used as the form recordsource for the staff data entry form in an HR database which joins three related tables, staff, ethnicity and disability.

As you will see from the (updatable) staff query excerpt, it is ordered not by staff reference number as such but by surname and forename, with reference number as a subsidiary sort criterion. Base the form on the table alone and you are stuck with the primary key sort order by default ([Ref No#] in the excerpt shown).

You don't have to use multiple tables - even with single-table queries you can define a suitable sort order to display the data for your users, and you can include calculated or specially-formatted fields for re-use in reports, for example.

When you join other tables you can display related field values without using DLookup or other domain functions to do so.

Try it - you won't regret it!

-Stewart

[code=sql]
SELECT Staff.[Row No], Staff.[Ref No#], Staff.[Payroll No], Staff.Title, Staff.Surname, Staff.[Former Surname], Staff.Forename, Staff.[Known As], Staff.[Other Names], Staff.Honorific , Staff.DOB, Staff.[Retiral Age], ..., [Ethnic Group].[Ethnic Group], Staff.[Address 1], Staff.[Address 2], ... , Staff.[Disability ID], Disability.[Disability Type], ..., Staff.[Reckonable Service Date]
FROM [Ethnic Group] INNER JOIN (Disability INNER JOIN Staff ON Disability.[Disability ID] = Staff.[Disability ID]) ON [Ethnic Group].[Ethnic Code] = Staff.Ethnic
ORDER BY Staff.Surname, Staff.[Known As], Staff.[Ref No#];
[/code]

[Coolboy55]

Hi CB55. If relationships are correctly defined between tables then multi-table queries using INNER, LEFT or RIGHT joins are normally updatable, as long as the joined fields are taken from the correct side of the relationships. Not once have I had to use an SQL Update or Insert statement with any form, in the 16 years I have been designing with Access. I don't tend to use complex table joins - appended below is an excerpt from a Staff query used as the form recordsource for the staff data entry form in an HR database which joins three related tables, staff, ethnicity and disability.

As you will see from the (updatable) staff query excerpt, it is ordered not by staff reference number as such but by surname and forename, with reference number as a subsidiary sort criterion. Base the form on the table alone and you are stuck with the primary key sort order by default ([Ref No#] in the excerpt shown).

You don't have to use multiple tables - even with single-table queries you can define a suitable sort order to display the data for your users, and you can include calculated or specially-formatted fields for re-use in reports, for example.

When you join other tables you can display related field values without using DLookup or other domain functions to do so.

Try it - you won't regret it!

-Stewart

[code=sql]
SELECT Staff.[Row No], Staff.[Ref No#], Staff.[Payroll No], Staff.Title, Staff.Surname, Staff.[Former Surname], Staff.Forename, Staff.[Known As], Staff.[Other Names], Staff.Honorific , Staff.DOB, Staff.[Retiral Age], ..., [Ethnic Group].[Ethnic Group], Staff.[Address 1], Staff.[Address 2], ... , Staff.[Disability ID], Disability.[Disability Type], ..., Staff.[Reckonable Service Date]
FROM [Ethnic Group] INNER JOIN (Disability INNER JOIN Staff ON Disability.[Disability ID] = Staff.[Disability ID]) ON [Ethnic Group].[Ethnic Code] = Staff.Ethnic
ORDER BY Staff.Surname, Staff.[Known As], Staff.[Ref No#];
[/code]

Wow, I was under the impression that a form based on a query could not be updated. Sigh... now I have a lot of corrections to make. :)

CB55