Solved

MS Access 2007 Query/Report Sort question

Posted on 2010-08-30
13
285 Views
Last Modified: 2013-11-28
DESIRED RESULT: I need a report that sorts my data by COMPANY name in ascending order. And then, by LASTNAME (within a Company if there are last names). Here's an example of what I want:

COMPANY                      LASTNAME
AllStateInsurance          
Bank of America             Alard
                                        Bernard
                                        Fitzgerald
                                        Hinds
                                        Young
Brown Construction        Brown
                                        Defazio
                                        McCapes
C&M Consulting
Citibank
...
Zim Industries
                                        Anderson
                                        Bennet
                                        Bentley
                                        ....
                                        Zimmerman
                                       
PROBLEM: Records where the Company name is "blank/empty/or Null" go to the top of my report and records are then sorted by Lastname.
So it looks like this:
COMPANY                    LASTNAME
                                     Anderson
                                     Bennet
                                     Bentley
                                      ....
                                      Zimmerman
AllStateInsurance          
Bank of America             Alard
                                        Bernard


I don't want records without a Company name to go to the beginning of the report. I think I need help with creating a simple expression to force the sort I want but I don't know how to do it.  Thanks in advance for your help!
0
Comment
Question by:MostHumbleNetman
  • 6
  • 4
  • 3
13 Comments
 
LVL 30

Expert Comment

by:hnasr
ID: 33563824
Did you try the sort on expression:

Nz(Company,"z")
0
 
LVL 74

Expert Comment

by:Jeffrey Coachman
ID: 33564241
I think hnasr has the solution you want.
One enhancement might be to hide the "Z" in the report.

But as hnasr suggested this first, I will give him the honor of embellishing on this, if he so chooses...
;-)


Just curious...
Why would a Company "Not" have a name?
How is this stored in the Company table?, with just the CompanyID?
In the US, in order to be a business "Entity" you must have a name, even if it is temporary.

JeffCoachman



0
 

Author Comment

by:MostHumbleNetman
ID: 33564270
This particular database is used for newsletters and fundraising letters. It contains under 5,000 records. The filelds for Company, MailAs, Salutation, JobTitle, LastName, FirstName, Address1, Address2, City, State, Zip, Phone, etc. are all in the same table. There are a couple of other tables such as contributions that are in other tables but the main data records are all in the table called tblMailList. So, to answer your question, it contains business as well as individual's information.

0
 

Author Comment

by:MostHumbleNetman
ID: 33564316
I apologize for asking what's probably a dumb question... but, do I go to my query for this report and put the info ... Nz(Company,"z") exactly as you've given it to me?  Here's are screen shots of my query. Perhaps this will help you to explain how I should set it up? Thanks again for your help.
ExistingQuery-ScreenShot.docx
0
 
LVL 74

Expert Comment

by:Jeffrey Coachman
ID: 33564350
<it contains business as well as individual's information.>
Ahhh. I see

Thanks for the clarification.

hnasr should be along shortly to help you with the NZ()

;-)

Jeff
0
 
LVL 30

Accepted Solution

by:
hnasr earned 125 total points
ID: 33566016
Sorting is done in report, not query
In the report design, select Sorting and Grouping, and sort on an expression: nz(Company,"z")

Thanks boag2000. Sorting in report, and using the z will put the company last without displaying it.
0
Enterprise Mobility and BYOD For Dummies

Like “For Dummies” books, you can read this in whatever order you choose and learn about mobility and BYOD; and how to put a competitive mobile infrastructure in place. Developed for SMBs and large enterprises alike, you will find helpful use cases, planning, and implementation.

 
LVL 30

Expert Comment

by:hnasr
ID: 33566021
If you have a company name za..., then use zzzzz....!
0
 
LVL 74

Expert Comment

by:Jeffrey Coachman
ID: 33567671
<Sorting in report, and using the z will put the company last without displaying it.>
Ah, OK, I thought that the NZ() was the field val.
But yes, if the "sort expression" is used, the Z should not display

Sorry about that, ...quick reading...

;-)

Jeff
0
 

Author Comment

by:MostHumbleNetman
ID: 33568360
I've uploaded a screen shot of my report in Design View. Where do I insert the expression you gave me?
nz(Company,"z")

CompanyReport-ScreenShot-DesignV.docx
0
 

Author Comment

by:MostHumbleNetman
ID: 33570037
I finally figured it out!
Here's what I did:
1. Open the Report in Design View (or open the report, right-click and select Design View)
2. Click on "Group and Sort" under the Design Tab
3. In the Group, Sort, And Total section (at the bottom of the Report, which is now in Design View), Click on the Arrow to the right of SORT BY and Select Sort by Expression (it's at the bottom of the list).
4. This will bring up the expression builder. I pasted the expression you provided me into the builder entry field nz(Company,"z") and hit ok, saved my report and hit View. It worked!

P.S. when I reopened the expression I'd pasted, I found that the expression builder automatically changed it to: =nz([Company],"z")    I later added the extra z recommended so that my final expression was: nz([Company],"zz")

 
0
 

Author Closing Comment

by:MostHumbleNetman
ID: 33570076
The only reason it wasn't easy to follow was because I am new to using expressions. But, after some trial and error I was able to correctly apply the answer to the problem and it worked.  
0
 
LVL 74

Expert Comment

by:Jeffrey Coachman
ID: 33570487
Soon you won't need us at all...

;-)
0
 

Author Comment

by:MostHumbleNetman
ID: 33570960
ha! I doubt that...but appreciate all your help. Thanks again!!
0

Featured Post

3 Use Cases for Connected Systems

Our Dev teams are like yours. They’re continually cranking out code for new features/bugs fixes, testing, deploying, testing some more, responding to production monitoring events and more. It’s complex. So, we thought you’d like to see what’s working for us.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

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…
A simple tool to export all objects of two Access files as text and compare it with Meld, a free diff tool.
Using Microsoft Access, learn some simple rules for how to construct tables in a relational database. Split up all multi-value fields into single values: Split up fields that belong to other things into separate tables: Make sure that all record…
In Microsoft Access, when working with VBA, learn some techniques for writing readable and easily maintained code.

910 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

18 Experts available now in Live!

Get 1:1 Help Now