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

report sorting - multiple fields in 1 level

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.

2 Solutions
Scott McDaniel (Microsoft Access MVP - EE MVE )Infotrakker SoftwareCommented:
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.
Dale FyeCommented:
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.
terpsichoreAuthor Commented:
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.
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?
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.
terpsichoreAuthor Commented:
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.
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Get 10% Off Your First Squarespace Website

Ready to showcase your work, publish content or promote your business online? With Squarespace’s award-winning templates and 24/7 customer service, getting started is simple. Head to Squarespace.com and use offer code ‘EXPERTS’ to get 10% off your first purchase.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now