Solved

report sorting - multiple fields in 1 level

Posted on 2014-02-02
5
451 Views
Last Modified: 2014-02-03
Dear experts -
Can I include multiple fields in a single report group level?
(GroupLevel(0).ControlSource = ...."

I want to concatenate 2 fields that are in the underlying record set and use this as one of the group levels.

Thanks!
0
Comment
Question by:terpsichore
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
5 Comments
 
LVL 84

Accepted Solution

by:
Scott McDaniel (Microsoft Access MVP - EE MVE ) earned 250 total points
ID: 39827513
You can combine those two values in your underling query, and then use that field as your sort. For example:

SELECT Account, FirstName & LastName AS FullName, Phone FROM Customer

This would create a field named "FullName" in your query, and you can use that as your group field.
0
 
LVL 47

Assisted Solution

by:Dale Fye (Access MVP)
Dale Fye (Access MVP) earned 250 total points
ID: 39827569
Can you give an example of what you are trying to concatenate?  

Unless it were a name, like Scott mentioned above, I would generally simply create two groupings in my report.  You can set the group detail section of the outer group to zero height and then put those two fields together in the 2nd groups detail section.
0
 

Author Comment

by:terpsichore
ID: 39827577
First - great responses. Let me clarify a bit more:
I actually want to keep the grouping exactly as it is; I just want to SORT with a little more refinement.
I have VBA code to define data grouping, which works just fine.
Is there some way to define the sortby separately? If so, in which event? I tried the following in the OPEN event, and it didn't seem to work.
Example:
Group 0 = company
Group 1 = manager
(We would like, indeed, to total on company, then total by manager)
But we would also like to SORT within the company on COMPANY and then by DIVISION within that company.
Make sense?
0
 
LVL 37

Expert Comment

by:PatHartman
ID: 39827827
You can add sort fields to the report definition.  Just be aware that if you move them above your group by, they will change the way the report is grouped.  So for example if you sort by customerID first and then group by State, you would have many instances of Connecticut  but if you add the sort by CustomerID After the group by state, you will have one group for each state and within that group, details will be sorted by customerID.

You can't do what you are asking in a simple report.  You would need to create a subreport since you are trying to display things in one sequence but summarize them on a different sequence.  Reports work on sort order.  If you are not ordering by a field, there is no way to total it.  If you group by Company, Sort by Division, and group by manager, you will have the manager repeat for each division if he has records in multiple divisions.  You will not have one single number for each manager.  If managers don't cross divisions, then just group by company, group by division, group by manager.  Then you can produce totals at each level.
0
 

Author Closing Comment

by:terpsichore
ID: 39828922
Two good ideas that helped me focus my thinking.
In the end Scott's solution wasn't exactly right, since I needed to preserve the grouping by the master sort. Creating a new custom field would have allowed me to sort correctly and use the VBA code nicely, but the fact was I didn't need to run totals on the secondary sort criterion.
The solution, in the end, was to add another grouping level - for MOST scenarios, the second grouping was IDENTICAL to the first - in one or two we used this new, inserted, group to specify the subsort.
Now it works perfectly.
Thank you.
0

Featured Post

PeopleSoft Has Never Been Easier

PeopleSoft Adoption Made Smooth & Simple!

On-The-Job Training Is made Intuitive & Easy With WalkMe's On-Screen Guidance Tool.  Claim Your Free WalkMe Account Now

Question has a verified solution.

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

Suggested Solutions

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…
A simple tool to export all objects of two Access files as text and compare it with Meld, a free diff tool.
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…
In Microsoft Access, learn how to use Dlookup and other domain aggregate functions and one method of specifying a string value within a string. Specify the first argument, which is the expression to be returned: Specify the second argument, which …

737 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