Sub-grouping in a Report (1 Viewer)

Good morning,

I have a table that has the following fields and field types:

Project_Code (Type: Short Text)
Project_Objective_Code (Type: Short Text)
Project_Objective_Statement (Type: Long Text)
Objective_Level_Indicator Code (Type: Short Text)
Objective_Level_Indicator_Statement (Type: Long Text)
Activity_Under_Objective_Code (Type: Short Text)
Activity_Under_Objective_Statement (Type: Long Text)
Activity_Level_Indicator_Code (Type: Short Text)
Activity_Level_Indicator_Statement (Type: Long Text)

I want to create a report grouped by:

1. Project Objective Code
2. Project Level Indicator Code
3. Activity Under Objective Code

When I do this, activities are grouped in project level indicator grouping. I have been working on it for two days, but no progress.

Attached is the table with example data. Thanks in advance for sharing your learning.

Thanks,
Dhamdard

Db does not include report. Please provide attempt.

Apologize. Attached is DB with the report.

When you open the report, you will see that:
- 1,2,3 with green background color are the objectives (in bold text)
- Text in blue are indicators under each objective.
- Text in dark with brown background is activity under each objective.
- Text in red is the indicator under each activity

The issue is:
- Indicator 1.1 and 1.2 should be listed one after another under objective 1. As you can see, indicator 1.2 is below because Activity 1.1.1 and its relevant indicators are in between.

Many thanks,
Dad

Can't see where it goes wrong, can you post a printscreen marked the problem?

Well, the data is sorting correctly - 1 comes before 2. I don't see what's wrong with report - the hierarchy is what I would expect. Why would you want 1.2 before 1.1.1?

The only example I don't understand is there are two 3.1.1, under 3.1 and 3.2. Data error in record?

I don't see anything colored green.

Apologize, I attached the correct version of DB now. Also, attached is the screenshot. I added two arrows in the screenshot where the problem is.

I still don't understand why you want that order. However, if you must, then need another field with sequence ID that can control the sort.

I am with a nonprofit and I voluntarily opted to give them a database so they could track their performance systematically. The reason why I want it in that order is the project log-frame in which the path way of change starts from top to bottom. For example, after the goal of a project, objectives is set, to measure progress towards objectives, specific outcome level indicators are set. Third layer of change is key activities under each objective. To measure progress towards each activity, specific indicators are set for each activity. To ease reporting pain, I want the database to run a report that is similar to the templates they use.

June7, i played all tricks I could including your advise but no luck.

Thanks,
Dhamdard

Well, you will have to provide your attempt for analysis.

Sorry, but that explanation makes no sense to me. I still don't know why 1.2 should come before 1.1.1. That would not be normal for this style of paragraph numbering. This style is one option in Word. You can see it demonstrated in any Wikipedia article. https://en.wikipedia.org/wiki/Outline_(list)

Should the title next to number 3 say THIRD instead of SECOND?

Consider normalizing data structure:

SELECT Project_Code, 1 AS Lvl, Project_Objective_Code AS Code, Project_Objective_Statement AS Statement FROM Tab_PMP
UNION SELECT Project_Code, 2, [Objective_Level_Indicator Code], Objective_Level_Indicator_Statement FROM Tab_PMP
UNION SELECT Project_Code, 3, Activity_Under_Objective_Code, Activity_Under_Objective_Statement FROM Tab_PMP
UNION SELECT Project_Code, 4, Activity_Level_Indicator_Code, Activity_Level_Indicator_Statement FROM Tab_PMP;

Note the missing underscore in fieldname [Objective_Level_Indicator Code].

UNION will not allow duplicate records which is why the query has only 21 records instead of 32. UNION ALL will allow duplicates and will have 32 records.