Grouping a report based upon a number expression

[bearcub]

I have a report that is grouped by "expression". Their are 4 groupings and each group has a number from 1 to 4. the first grouping is grouped by the expression "=4". The second grouping is grouped on the expression "=2". The 3rd grouping, the expression is "=3" and the 4th grouping is "=1".

How do you create these expressions so you can group on them. I thought that you group on fields (I'm new to Access) but you can apparently group on an expression.

In report view, the headers are labeled like this:
=4 Header
=2 Header
=3 Header
=1 Header

How does Access create these headers?


Thank you for your help,

Michael

 

[xenou]

Since you have the groups, it sounds like you created them ... but you also seem surprised that they are there now, which is a little confusing. Do you want these groups? Did you create them? If so, how did you create them? What exactly do you mean by an expression? For instance, 'where Dept = 101' is an expression in the statement select * from Departments where Dept = '101'. But most people wouldn't call that grouping on an expression. They'd just say grouping by Dept. If your headers are labeled =1, =2, and so on, it's probably because they have not been given more meaningful names, that's all.

Reports offer a wealth of grouping, layout, labeling, and summarizing features that go far beyond the simple mechanics of a sql query. Try to separate out what comes from your data (this is what you should know very well yourself) and what is just the presentation features (which is what your users want to be familiar with, whether or not they understand the source data).

 

[bearcub]

I didn't build the DB, I'm trying to emulate what was done and modify the queries and reports as needed. It looks like the person who created the report wanted to create and alternate rows affect (white, dark, white) with 3 labels. The lables contain office locations that are to appear only once (so they won't appear in the detail section).

She created these groupings using an expression so she could insert a label in that section.

Have you heard of this being done before?

Michael

 

[xenou]

Expressions are all over the place in database queries, forms and reports so it's not clear to me what you mean. Sounds reasonable that this can be done but to be honest I have no idea what exactly you mean by an expression other than the obvious (you are seeing headers that say =1 =2, =3 which presumably match some kind of ordering in the data). Since it's doing banding of three colors that might matches to a mod function so you can create the bands.

 

[bearcub]

Yes, I agree.

It is confusing as to why the report is being sorted by the term "expression" (and the 3 expressions in the multiple groupings are created by using the numbers 1,2,3,4). It is being used to create alternate row coloring for labels - so they only appear once on he first page. I don't have a lot of experience using Access and this grouping is totally confusing (among other things as well). I will probably have to talk to the person who created it but I'm not sure is she will remember why she did what she did or can show me how she did it.

The report is set up using labels for 3 different organizations followed by the members (members are in the detail section of the report). The labels are in the 3 header sections (group headers). The top header has a white background, the second header has a gray background and the 3rd header grouping as a white background. When it is in report view the report provides alternate shading for the labels so it has an Excel table effective (alternative row shading). That's why the grouping feature is used in this report. The grouping is just a workaround to provide alternate shading for the labels.

I'll continue trying to find out how she created it. At this point, I'm stumped.

Thank you for your help,

Michael

 

[xenou]

Are you trying to do something other than find out how she did this? Like ... remove the banding? Or do the same thing in another report?

 

[bearcub]

Yes, I'm trying to find out how she did this. The banding is effective - I'm trying to copy what she did so i can create a similar report using her method.

 

[xenou]

What version of Access are you using?

 

[bearcub]

2016 - Office 365

 

[xenou]

Okay, I tried searching for banding options for that version of Access but can't find anything. So it might be something that the Access developer put together rather than an inbuilt report feature.

Note - I did find a reference to color banding on data rows - so possibly that can be done relatively simply for a detail section. But can't say for sure if there is any such thing for other sections besides detail sections.