?
Solved

report sorting - multiple fields in 1 level

Posted on 2014-02-02
5
Medium Priority
?
459 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 85

Accepted Solution

by:
Scott McDaniel (Microsoft Access MVP - EE MVE ) earned 1000 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 48

Assisted Solution

by:Dale Fye
Dale Fye earned 1000 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 38

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

VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

Question has a verified solution.

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

Preparing an email is something we should all take special care with – especially when the email is for somebody you may not know very well. The pressures of everyday working life stacked with a hectic office environment can make this a real challen…
This article shows how to get a list of available printers for display in a drop-down list, and then to use the selected printer to print an Access report or a Word document filled with Access data, using different syntax as needed for working with …
With Microsoft Access, learn how to start a database in different ways and produce different start-up actions allowing you to use a single database to perform multiple tasks. Specify a start-up form through options: Specify an Autoexec macro: Us…
Access reports are powerful and flexible. Learn how to create a query and then a grouped report using the wizard. Modify the report design after the wizard is done to make it look better. There will be another video to explain how to put the final p…
Suggested Courses

765 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