Solved

Access 2007 Grouping question

Posted on 2011-03-25
5
247 Views
Last Modified: 2012-06-21
Hello,

I am working on creating a repot.  I need to add grouping to the report.  I know how to group on a field where ther are like terms... ie group on city name..

but... here is my question that I need help on..

I need to add grouping based on a value of a field.....

Each record has a rating number... 1 - 60...

I need to group.. ratings 44-60 togther
35 to 44 together
27 to 34 together
15 to 26 togther
0 to 14 togther

how can I do this grouping...

0
Comment
Question by:FaithDan
5 Comments
 
LVL 16

Expert Comment

by:Sheils
ID: 35219446
Use a query with BETWEEN condition

eg:

SELECT * FROM TableName
WHERE Fieldname
BETWEEN 0 AND 14

0
 
LVL 16

Accepted Solution

by:
Sheils earned 500 total points
ID: 35219466
How you set it will depend on you report layout.

You may have a series of subreports each with its own query having different between condition

Alternatively you can bundle the between queries into a union query and display the result in single or multiple records report
0
 
LVL 1

Author Closing Comment

by:FaithDan
ID: 35219485
thanks...got it...
0
 
LVL 30

Expert Comment

by:hnasr
ID: 35219519
You may group on a calculated field in the query, named Range
Assume the field name: f1

Range: IIf([f1]>=44,"44-60",IIf([f1]>=35,"35-43",IIf([f1]>=27,"27-34",IIf([f1]>=15,"15-26","0-14"))))

In the report add group on Range
0
 
LVL 18

Expert Comment

by:Richard Daneke
ID: 35219538
The group feature in reports (AC2007) can group by 5s 10s 100s 1000s or fixed custom intervals.  You have a variable grouping.  So,

I would create a new field in a query to id the group with a compound IIF statement and group on this ID.  For Example, in QBE grid field name enter:

Group : IIF([Rating] < 14, "Group1",IIF([Rating] < 27, "Group2",IIF([Rating] < 35, "Group3",IIF([Rating] < 45, "Group4", IIF([Rating] < 61, "Group5", "UnGrouped")))))

This new field in your report query can now be used for report grouping.
0

Featured Post

How to improve team productivity

Quip adds documents, spreadsheets, and tasklists to your Slack experience
- Elevate ideas to Quip docs
- Share Quip docs in Slack
- Get notified of changes to your docs
- Available on iOS/Android/Desktop/Web
- Online/Offline

Join & Write a Comment

Regardless of which version on MS Access you are using, one of the harder data-entry forms to create is one where most data from previous entries needs to be appended to new records, especially when there are numerous fields and records involved.  W…
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, learn how to “cascade” or have the displayed data of one combo control depend upon what’s entered in another. Base the dependent combo on a query for its row source: Add a reference to the first combo on the form as criteria i…

707 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

11 Experts available now in Live!

Get 1:1 Help Now