Link to home
Start Free TrialLog in
Avatar of Biofilminc
Biofilminc

asked on

Group more than four fields in Access 2013

I am creating a report in access. When I create the report I can only create 4 grouping levels in the wizard. I have more than 4 fields I want to group. How can I group the rest? My report will have about 10 fields the same and another field that shows the HDD names. Some server have multiple HDDs so I get duplicate data. The attached picture shows the first 5 fields. How can I group the 5th. I went to "Group and Sort" but it did nothing. User generated image
Avatar of Jeffrey Coachman
Jeffrey Coachman
Flag of United States of America image

Reports with more than four grouping levels are difficult to interpret (hence the limit)

If you need more, you will have to go into design view and add them.
Right-click on the report in design view and select "Sorting and Grouping"
Then click "Add a Group" as needed.
Can we ask why so many grouping levels are needed in one report?
Perhaps there is a better alternative?
I went to "Group and Sort" but it did nothing.
You can sort and group by up to 10 fields in A2003+
The wizard creates header/footer structure for only up to 4.
You can create others but you must then specify if you want a header/footer or both, and manually move the controls into the newly created sections

In Group and Sort, add the desired field
Specify Group Header and/or Group Footer as appropriate.
The sections will appear on the report
Populate them with appropriate controls.

Why you'd want that can be a conundrum.
Each section should have in it controls that vary based solely on the field chosen for the sort/grouping.

Seldom do you actually want so many levels -- and it usually makes for a very empty (lots of whitespace) report.
Avatar of Biofilminc
Biofilminc

ASKER

Then how do people make reports?  I just don't want to see duplicate data. Didn't know it was such a weird thing to ask for.

As I said "Then click "Add a Group" as needed." does nothing.


"Why you'd want that can be a conundrum.
Each section should have in it controls that vary based solely on the field chosen for the sort/grouping."

What do you mean?
ASKER CERTIFIED SOLUTION
Avatar of Biofilminc
Biofilminc

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Then how do people make reports?  I just don't want to see duplicate data. Didn't know it was such a weird thing to ask for.
It isn't.
Few people do it because if you google up 'MS Access HideDuplicates' you don't tend to find a lot of detail.
But that isn't what you asked.
You asked how you would add another grouping to a reportUser generated imageThis report has 4 levels
Only two involve header/footers
I could add more -- but as you can see, all of my controls are in the Detail section.
This first four have HideDuplicates set to true.
I use the headers and footers to drive events and they have very skinny 0.0007" lines in them.

Each section should have in it controls that vary based solely on the field chosen for the sort/grouping.
This particular example doesn't lend itself to that, but if I had a bunch of controls from the table ToolTypeName comes from, I'd likely put them all in the TooltypeName header.  Similarly for SerialNumber.

Grouping isn't really the best way of making controls invisible as it eats up a ton of room on the report.
Hide Duplicates can be much better in using the paper well.
'Add a Group' only creates something else to sort by.
YOU have to add headers/footers and move controls to actually get the grouping done.
And the wizard is usually stupid in that it throws a single control into each group -- but then, the wizard was created to make a very singular kind of layout.

In my example, I have lots to sort by, and events I want going on (the need for headers/footers) but all my controls can stay on a single line.
If the grouping is simply to hide duplicates, the best solution is to do the grouping in the query.
Would you have an example of how to do it in the Query? I have had no success that way and that is one reason I had to use the Report Wizard
Select Server, ServerName, IPaddress, Model, OS, count(*) as GroupCount
From yourtable
Group By Server, ServerName, IPaddress, Model, OS;
This was my solution to my own problem
It is done and I don't care about points, I care about veracity for people searching later
I have more than 4 fields I want to group. How can I group the rest?
That was your question.
Well I found the "Hide Duplicates" option that I was looking for.
Hide Duplicates has absolutely nothing to do with grouping and sorting, and would tell no future reader anything about how to create grouping levels 5 through 10 on a report.

I am glad you got your operational problem fixed and did not need to use grouping and sorting.
You still don't seem to understand how to use grouping and sorting, though.

NIck67