Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 469
  • 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.

Thanks!
0
terpsichore
Asked:
terpsichore
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.
0
 
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.
0
 
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.
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
 
PatHartmanCommented:
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
 
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.
0

Featured Post

Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

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