Solved

MS Access 2007 Query/Report Sort question

Posted on 2010-08-30
13
284 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
Backup Your Microsoft Windows Server®

Backup all your Microsoft Windows Server – on-premises, in remote locations, in private and hybrid clouds. Your entire Windows Server will be backed up in one easy step with patented, block-level disk imaging. We achieve RTOs (recovery time objectives) as low as 15 seconds.

 
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

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

QuickBooks® has a great invoice interface that we were happy with for a while but that changed in 2001 through no fault of Intuit®. Our industry's unit names are dictated by RUS: the Rural Utilities Services division of USDA. Contracts contain un…
PL/SQL can be a very powerful tool for working directly with database tables. Being able to loop will allow you to perform more complex operations, but can be a little tricky to write correctly. This article will provide examples of basic loops alon…
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.
What’s inside an Access Desktop Database. Will look at the basic interface, Navigation Pane (Database Container), Tables, Queries, Forms, Report, Macro’s, and VBA code.

708 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

15 Experts available now in Live!

Get 1:1 Help Now