Solved

Need Access 2007 Report to sort ASC

Posted on 2010-09-13
7
611 Views
Last Modified: 2012-05-10
Greetings:

I have created a report in an Access 2007 database. My Report source SQL statement is below. In the attached screenshot, you can see that teh report is a listing of employees by Manager. The Manager field is being fed by the alias shown below. I want the Managers to sort ASC by the Manager's last name. I tried adding ASC to the statement below, with no luck. Any idea what I am doing wrong?

SELECT tbl_Employees.*, tbl_Managers.[Lname] & ", " & tbl_Managers.[Fname] AS MgrName
FROM tbl_Managers INNER JOIN tbl_Employees ON tbl_Managers.ID = tbl_Employees.ManagerID
ORDER BY tbl_Managers.Lname, tbl_Managers.Fname;

reportscreenshot.jpg
0
Comment
Question by:smsdesigns
  • 3
  • 2
  • 2
7 Comments
 
LVL 47

Accepted Solution

by:
Dale Fye (Access MVP) earned 167 total points
ID: 33661707
The sort order in the SQL statement is of no use.  You need to set up a Sort Order within your report.

Opent the report in design view, click the Group&Sort icon on the ribbon bar.

Click the Add a Sort button. then select the appropriate field from the dropdown list.
0
 
LVL 119

Assisted Solution

by:Rey Obrero
Rey Obrero earned 167 total points
ID: 33661734
in the design view of the report, click on the Sorting and Grouping and enter the Field name in the Sorting  and Grouping Window
0
 
LVL 19

Assisted Solution

by:MINDSUPERB
MINDSUPERB earned 166 total points
ID: 33661751
smsdesigns,

The steps below may help you achieve what you want for your report:

1. In Report Design View, Click the design tab
2. Select Group and Sort in Grouping and Totals Group
3. You can work around grouping and sorting in the Group, sort and Total window at the bottom part of the window.

Do hope those steps help you.

Sincerely,

Ed
0
Highfive + Dolby Voice = No More Audio Complaints!

Poor audio quality is one of the top reasons people don’t use video conferencing. Get the crispest, clearest audio powered by Dolby Voice in every meeting. Highfive and Dolby Voice deliver the best video conferencing and audio experience for every meeting and every room.

 
LVL 119

Expert Comment

by:Rey Obrero
ID: 33661758
you may need to include to your SQL statement the fields LName and FName


SELECT tbl_Employees.*, tbl_Managers.[Lname] & ", " & tbl_Managers.[Fname] AS MgrName, LName,FName
FROM tbl_Managers INNER JOIN tbl_Employees ON tbl_Managers.ID = tbl_Employees.ManagerID
0
 
LVL 19

Expert Comment

by:MINDSUPERB
ID: 33661783
smsdesigns,

Please disregard my post. It's a duplication with other EE's post.

I am sorry fyed and carpricorn1.

Sincerely,

Ed
0
 
LVL 119

Expert Comment

by:Rey Obrero
ID: 33661798
NP...
0
 
LVL 47

Expert Comment

by:Dale Fye (Access MVP)
ID: 33661839
Happens all the time.  

What I hate is when I start a response when there are no others posted, get sidetracked, and 30 minutes later complete my response and hit send, then see that 3 or 4 others have already posted the same response.  
0

Featured Post

Enabling OSINT in Activity Based Intelligence

Activity based intelligence (ABI) requires access to all available sources of data. Recorded Future allows analysts to observe structured data on the open, deep, and dark web.

Join & Write a Comment

Experts-Exchange is a great place to come for help with solutions for your database issues, and many problems are resolved within minutes of being posted.  Others take a little more time and effort and often providing a sample database is very helpf…
In a multiple monitor setup, if you don't want to use AutoCenter to position your popup forms, you have a problem: where will they appear?  Sometimes you may have an additional problem: where the devil did they go?  If you last had a popup form open…
Familiarize people with the process of utilizing SQL Server stored procedures from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Micr…
Basics of query design. Shows you how to construct a simple query by adding tables, perform joins, defining output columns, perform sorting, and apply criteria.

705 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

17 Experts available now in Live!

Get 1:1 Help Now