How do you sort contatenated fields on a report?

dijilator

Registered User.
Local time
Today, 00:14
Joined
Oct 24, 2014
Messages
14
I have a class roster report in which the Employees' names are concatenated in a text box. When I select to sort the concatenated names alphabetically, it is sorting by the Unique ID, which is a letter and four numbers. The letter used to match the last name, but newer IDs are random. So most names are still in order, but some are not.

The Employee field Row Source is:

SELECT [Last_Name] & ", " & [First_Name] AS Employee, tbl_AM_Operators.User_ID FROM tbl_AM_Operators ORDER BY [Last_Name] & ", " & [First_Name];

What do I need to change to have the Employees' names alphabetized on the report?
 
Use the Order and Grouping features in the Report designer.

Order By clauses don't hold for reports.
 
Galaxiom, I removed the Order by in the query for the field and tried the Grouping and Order function on the report, no dice - still won't alphabetize the names. Would you mind looking at this sample database to see what I might need to change? I don't necessarily need the names concatenated on the report, as long as both the last and first names are on the report and I can alphabetize them.

The report is named, rpt_Class_Roster2,

and the field is, User_ID_fk, in the detail section of the report.
.
 

Attachments

Remove all lookup fields from your tables - once that is done post again if still in need.
Lookups have no business in tables , only cause confusion. Use lookups on forms.
 
I think your sorting issue is related to the table lookup at the field level of the table.

see http://access.mvps.org/access/lookupfields.htm

OOPs: I see spike has posted while I was having issues with the sort, and traced it to the lookup fields.
 
Hello experts, I appreciate your feedback. I actually laid out the structure of the database in the best way that I knew how. And that is pretty much akin to the only way I know. Most of my database is built with lookups. If, with your advice, you could show me how to do what is needed without messing up the rest of the functions that rely on the initial build, that would help. Maybe explain how I need to restructure the database to fit what you describe. If that is too difficult, perhaps you would point me to some reading that shows how to make the table relations necessary to arrive at the same use that I employ with the lookups.
 
Last edited:
I'm trying to understand how to implement what has been suggested. If I remove the user ID foreign key from the class roster table, where would I store the information of who attended the class? To my understanding, we don't want to build tables of duplicate information, in this case, duplicate employee information.
 
Perhaps an expert could take one of my tables in the posted database and set it up with a form the way spikepl indicated so that I can understand how to do what he suggests with the rest of the database.
 

Users who are viewing this thread

Back
Top Bottom