• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 295
  • Last Modified:

MS Access 2007 Query/Report Sort question

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
MostHumbleNetman
Asked:
MostHumbleNetman
  • 6
  • 4
  • 3
1 Solution
 
hnasrCommented:
Did you try the sort on expression:

Nz(Company,"z")
0
 
Jeffrey CoachmanMIS LiasonCommented:
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
 
MostHumbleNetmanAuthor Commented:
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
Never miss a deadline with monday.com

The revolutionary project management tool is here!   Plan visually with a single glance and make sure your projects get done.

 
MostHumbleNetmanAuthor Commented:
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
 
Jeffrey CoachmanMIS LiasonCommented:
<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
 
hnasrCommented:
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
 
hnasrCommented:
If you have a company name za..., then use zzzzz....!
0
 
Jeffrey CoachmanMIS LiasonCommented:
<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
 
MostHumbleNetmanAuthor Commented:
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
 
MostHumbleNetmanAuthor Commented:
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
 
MostHumbleNetmanAuthor Commented:
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
 
Jeffrey CoachmanMIS LiasonCommented:
Soon you won't need us at all...

;-)
0
 
MostHumbleNetmanAuthor Commented:
ha! I doubt that...but appreciate all your help. Thanks again!!
0

Featured Post

Take Control of Web Hosting For Your Clients

As a web developer or IT admin, successfully managing multiple client accounts can be challenging. In this webinar we will look at the tools provided by Media Temple and Plesk to make managing your clients’ hosting easier.

  • 6
  • 4
  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now