MS Access Report: Two fields into One Group Header?

[fatogre]

Here is the problem I have. I am creating a Segregation of Duties report that show users with two different roles that they are assigned to in a system, that can potentially create a conflict.

Table:
Username | Role1 | Role 2

Role1 and Role2 are variables. They can be any number of combinations of different roles.

I am trying to create a report that shows.......

Role1 (One combination instance of Role 1 and Role 2)
Role2
Username#1
Username#2
Username#3...etc

The only way I can think of is that the GROUP HEADER needs to be TWO fields, not just one. When I use just one field (e.g., Role1), it doesn't display ALL instanances where Role2 may be different. It groups just Role1 and displays table rows, even though Role2 may be different. I hope that makes sense.

Please let me know if anyone can help!!!! Thank you!

 

[AlexCuse]

Can you lay out some sample data?

The way I understand it, I don't see why you need a Group Header at all?

You may try making your group by/header something that role 1 and role 2 have in common?

Professor: But what about your superintelligence?
Gunther: When I had that there was too much pressure to use it. All I want out of life is to be a monkey of moderate intelligence who wears a suit. That's why I've decided to transfer to Business School.
Professor: NOOOOOOOOOOOO.

 

[fatogre]

Does this help?

[COLUMNS]
ROLEUSER|Role1|Role2

[DATA]
JOE|BENEFITS MANAGER|PAYROLL ENTRY
JOE|BENEFITS MANAGER|PAYROLL PROCESS
MARY|BENEFITS MANAGER|PAYROLL AP VENDOR
MARY|BENEFITS MANAGER|PAYROLL QUERY

 

[AlexCuse]

So you want to group names, based on the combination of row 1 and row 2? If so, I think you would just go to sorting and grouping and specify that you want to group by both, or maybe a combination of the two?

I would create an additional field in your report source query called KEY and set it equal to Role1 & Role2, then you can group on KEY without displaying it.

Hope this helps,

ALex

Professor: But what about your superintelligence?
Gunther: When I had that there was too much pressure to use it. All I want out of life is to be a monkey of moderate intelligence who wears a suit. That's why I've decided to transfer to Business School.
Professor: NOOOOOOOOOOOO.

 

[fatogre]

I saw two recommendations...and one of them works perfectly!

...."If so, I think you would just go to sorting and grouping and specify that you want to group by both, or maybe a combination of the two?".....

I don't think Access has this functionality.

...."I would create an additional field in your report source query called KEY and set it equal to Role1 & Role2, then you can group on KEY without displaying it.".....

This works great! Thank you!

 

[AlexCuse]

Yeah I realized as I was typing that that might not work. I try to do most of my grouping and data manipulation in source queries rather than in the report itself, hence my ineptitude when it comes to the functions within the report.

Anyway, I'm glad it helped you out!

Take Care,

Alex

Professor: But what about your superintelligence?
Gunther: When I had that there was too much pressure to use it. All I want out of life is to be a monkey of moderate intelligence who wears a suit. That's why I've decided to transfer to Business School.
Professor: NOOOOOOOOOOOO.

 

[pbrodsky]

Certainly you can group a report on 2 fields. Just don't create a group header for the 1st grouped field and put output fields for both the 1st and 2nd grouped fields in the header for the 2nd.