Results 1 to 7 of 7
  1. #1
    Lounger
    Join Date
    Jul 2002
    Location
    Dallas, Texas, USA
    Posts
    43
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Sort names in report if expression joins first/las (XP)

    I'm almost done (I think). One of the query based reports in the directory I've created uses a combo box to list companies with their employee representatives under the company name. I can't get these names to sort by last name-they only want to sort by first name in either ascending or descending order. The employee name field is an expression joining first name and last name, and the first name displays first. Is there anyway a nonprogrammer can get these names to sort in alphabetical order by last name. I tried throwing the lastname field into the query, unchecking the show box and sorting that field by ascending order, but that didn't do squat.

    To complicate matters, there are four individuals not associated with a firm or company whose names display at the beginning of the report. In sort/grouping, I've got the names sorted ascending, but their names are always sorted the opposite of what I've selected (by first name, of course), while the names associated with a company name all sort by first name in whatever order I specify.

    To complicate matters even further, I want those four individuals to be listed at the end of the report instead of the beginning. I added a y/n field to my table entitled company affiliation and entered y for individuals with company affiliations and n for those without. I brought that field into my query and sorted by that field, but no matter what I did, I couldn't get those folks to move to the end of the report.

    Would it help, and if it would, is there a way to do a primary and secondary sort on the table underlying the query on which this report is based, with the primary sort field being Company Affiliation and the secondary sort field being lastname?

    ...and if I can get them there, is there any way to add a title to that group: No Company Affiliation

    I'm thinking maybe I should duplicate the table and enter "No Company Affiliation" (without quotes, of course) in the cell which contains company name for the other members and use that table as the basis for the query underlying this report. Is that the best/easiest way for a nonprogrammer to do it?

    Sorry, but you've probably figured out if you've read any of my other posts that Access for Idiots (several steps below Access for Dummies) would be too technical for me. Someone ask Woody to write a book entitled "Practical Access." I guarantee I can ask him any question he wants to address in the plain English we nontechies use when we want to know something, and he can translate it to access terminology while explaining what button to push and what to type anywhere we need to type something.

    I promise I looked this up (or tried to look this up) in help first.

    Rachel

  2. Get our unique weekly Newsletter with tips and techniques, how to's and critical updates on Windows 7, Windows 8, Windows XP, Firefox, Internet Explorer, Google, etc. Join our 480,000 subscribers!

    Excel 2013: The Missing Manual

    + Get this BONUS — free!

    Get the most of Excel! Learn about new features, basics of creating a new spreadsheet and using the infamous Ribbon in the first chapter of Excel 2013: The Missing Manual - Subscribe and download Chapter 1 for free!

  3. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 16 Times in 16 Posts

    Re: Sort names in report if expression joins first/las (XP)

    I don't understand why or how you are using a combo box "to list companies with their employee representatives under the company name" in a report.

    A known issue with Access is that a report ignores the sorting order you specify in the control source of the report. You must specify the sorting order in the Sorting and Grouping window. If I wanted to list employees within companies, I would add the following to the Sorting and Grouping window:
    <UL><LI>The yes/no field.
    <LI>The company name field.
    <LI>The last name field.
    <LI>The first name field.[/list]To display "No company affiliation" for records that have no company name, there are several solutions. Don't copy the table - keeping the copies synchronized would be a nightmare.
    <UL><LI>Create a query based on the table and use that as record source of the report. Add all fields you need to the query grid (or drag * into the query grid). In the next empty column, enter the following expression into the first (Field) row:
    CN: Nz([CompanyName], "No Company Affiliation")
    You must substitute the actual name of the company name field for CompanyName. The Nz function will use the first argument (company name) if it is not null (empty), and use the second argument ("No Company Affiliation") if the first argument is null.
    Now, you can use CN in your report.
    <LI>It is also possible to use the Nz function directly in the report: create a text box and set its Control Source property to
    =Nz([CompanyName], "No Company Affiliation")
    Here, too, you must substitute the actual name of the company name field for CompanyName.[/list]Note: Access is a developer tool. As such, it is very poweful, but also quite complicated. It is not really an end-user program such as Word or Excel; end-users should only work with applications created in Access. If you want to develop Access applications, you should follow some courses.

  4. #3
    Star Lounger
    Join Date
    Mar 2001
    Location
    Dudley, Midlands, England
    Posts
    52
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Re: Sort names in report if expression joins first/las (XP)

    Rachel, you're quite right in principal in what you're doing - by sorting on the last name separately - but there are a few things you need to bear in mind too. Firstly, if you're using a query behind a report then the only time the query's sort order is used is if there is absolutely no sorting or grouping defined in the report itself. As soon as you need any specialist sorting or grouping - e.g. in this case, grouping by company - the query sequence goes out of the window so you need to add the sort criteria via the sorting and grouping box in report design mode. In your case, just pass the surname back in the query but don't put it on the report anywhere and insert it below the line where you've defined the company grouping.

    As regards the individuals not associated with a company, you could try this: I'm assuming that on the people records there is an ID or other kind of pointer to a company record and for those not associated with one this will be null. If so, create a new output field in the query along the lines of RecordType: IIf(IsNull([frgnCompanyID]),1,0) so that will return a 0 for each record with a company and 1 for those without. Make this field your first sort key in the report (i.e. before the grouping by company) and it will put all the company people records first and the non-company people afterwards.

    Hope this helps and makes some kind of sense.

    Simon.

  5. #4
    Lounger
    Join Date
    Jul 2002
    Location
    Dallas, Texas, USA
    Posts
    43
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Sort names in report if expression joins first/las (XP)

    Hi, Hans. Thanx for your reply. No, I don't know how or why I used a combo box either. I realize now I could've used the company header for what I want - I'll look more closely at your and Simon's ideas when I get home tonight - looks like they'll both work fine for what I need, so it'll just be a matter of making a choice.

    Hans, I understand your frustration with people like me; but there's a subculture of us out there (accountants, engineers, etc.) who are either self employed or work for small businesses who do not have budget or much need for developers/programmers, but we've used Acccess for years to store and extract data (Excel still has a limit on the number of records it will hold, so trying to stuff a 578,000 record table into it via Microsoft Query isn't gonna work!). I, like the rest of them, have taken numerous Access courses, and we WANT to remember what we were taught when we got to the report writing, but we get a lot thrown at us <img src=/S/bananas.gif border=0 alt=bananas width=33 height=35> at one time and figure it'll make more sense when we start using it in conjunction with our own data and needs. Then, the need arises once in a blue moon-like this deal I'm working on - I haven't needed to do an Access report since 1999- and voila! you get posts from seemingly total idiots <img src=/S/innocent.gif border=0 alt=innocent width=20 height=20> like me who have a tremendous amount of knowledge about very little in Access who throw ourselves on the mercy of those of you who are so kind as to give us a helping hand. And, unfortunately for you, as an old boss used to tell me, "You'll get your reward in the next life." <img src=/S/angel.gif border=0 alt=angel width=15 height=21> But I am VERY grateful, FWIW.

    As for access being a developer's tool, well, I've been "developing" every night this week, although rest assured I'd never in a million years put Access Developer on my resume! Perhaps it us unfortunate that microsoft did not include the warning label "Do Not Try this At Home" on the Product Box. <img src=/S/2cents.gif border=0 alt=2cents width=15 height=15>

    Anyway, I'm doing this project for a professional organization of which I am membership chairman and have to give camera ready copy to the printer tomorrow, so I'll be out of everybody's hair for perhaps a very long while after that (but who knows!). <img src=/S/evilgrin.gif border=0 alt=evilgrin width=15 height=15>

    Once again, I very much appreciate the time you and everybody else who's responded to me has taken to advise me as I create the roadmap while traveling.

  6. #5
    Lounger
    Join Date
    Jul 2002
    Location
    Dallas, Texas, USA
    Posts
    43
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Sort names in report if expression joins first/las (XP)

    Thanx, Simon. I'll try to absorb all this tonight when I get home (which is where what I'm working on is!).

    Rachel

  7. #6
    Plutonium Lounger
    Join Date
    Dec 2000
    Location
    Sacramento, California, USA
    Posts
    16,775
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Sort names in report if expression joins first/las (XP)

    Rachel,

    Part of the problem is that Microsoft has marketed Access as a tool that anyone could use to develop their own databases and they continue to deny it any real status as a developer's tool because they don't want to scare off purchasers. Unforunately, they neglect to mention that if you don't understand the principles of relational database design, you'll wind up being extremely frustrated and often build something that could have been done more easily in Excel! <img src=/S/woops.gif border=0 alt=woops width=58 height=36>
    Charlotte

  8. #7
    Lounger
    Join Date
    Jul 2002
    Location
    Dallas, Texas, USA
    Posts
    43
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Sort names in report if expression joins first/las (XP)

    I certainly agree with what you're saying about Microsoft's marketing. A clue I had early on was back in the days. MS gave free unlimited tech support for all products except Access, where it provided 1 or 2 support incidents for free.

    Of course, I don't think anybody uses all features of any product (maybe I'm wrong) except the professional developers, but access is quite suitable for many things one does not need to be a professional developer to do. If it weren't, the subculture and I wouldn't be using it. You are absolutely right (like you really need for me to agree with you!) that one must understand the principles of relational databases, but there is a difference between understanding the principles and knowing which technical tool to use to effect an outcome and after knowing, how to use it. A lot of it's terminology (e.g., typing page size in help and getting page member value as a result with no explanation!)

    Anyway, again, I appreciate all the wonderful and right on target help I got from everybody. Hans's and Simon's suggestions were both incredibly helpful (I incorporated most of what both of them offered (picking one where there were alternatives) into my wrap up, have the darned thing printed (and it looks so beautiful!), and will turn it over to the printer at noon.

    I will be spending time on and off until next August enhancing my database skills as relates to this project because I see all kinds of possibilities here.

    Thanx again to you, Wendell, Hans, and Simon. <img src=/S/bravo.gif border=0 alt=bravo width=16 height=30>

    Rachel

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •