Solved

report sorting - multiple fields in 1 level

Posted on 2014-02-02
5
438 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
5 Comments
 
LVL 84

Accepted Solution

by:
Scott McDaniel (Microsoft Access MVP - EE MVE ) earned 250 total points
Comment Utility
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
Comment Utility
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
Comment Utility
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 34

Expert Comment

by:PatHartman
Comment Utility
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
Comment Utility
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

Get up to 2TB FREE CLOUD per backup license!

An exclusive Black Friday offer just for Expert Exchange audience! Buy any of our top-rated backup solutions & get up to 2TB free cloud per system! Perform local & cloud backup in the same step, and restore instantly—anytime, anywhere. Grab this deal now before it disappears!

Join & Write a Comment

Overview: This article:       (a) explains one principle method to cross-reference invoice items in Quickbooks®       (b) explores the reasons one might need to cross-reference invoice items       (c) provides a sample process for creating a M…
I see at least one EE question a week that pertains to using temporary tables in MS Access.  But surprisingly, I was unable to find a single article devoted solely to this topic. I don’t intend to describe all of the uses of temporary tables in t…
Get people started with the utilization of class modules. Class modules can be a powerful tool in Microsoft Access. They allow you to create self-contained objects that encapsulate functionality. They can easily hide the complexity of a process from…
In Microsoft Access, when working with VBA, learn some techniques for writing readable and easily maintained code.

763 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

Need Help in Real-Time?

Connect with top rated Experts

10 Experts available now in Live!

Get 1:1 Help Now