counting record from subreport to the main report sorted by group level

[arnold71]

I've been searching the internet for solution on this and I'm unlucky to get a direct/ simple answer. (Actually I almost got one but the thread discontinued).:banghead:

Here's the situation. I have 2 tables, One holding the information on a project (project) such as its location (Region, Province, District). But each project covers several municipalities, therefore I created a municipality table (municipality) that holds the names of the municipalities covered by the projects. The two tables are linked by the field name ProjID (project ID as primary key) and its relationship, one to many.

I need to create a report grouped/sorted according to Region first, then Province, then District, then by Project ID No. in each district. Each levels/group showing the total number of municpalities covered. Here's the illustration:

Region/Province/Dist Proj ID/Project Title No. of Municipalities
Region1 19
Province1 15
District1 10
1 Project Title 5
2 Project Title 5
District2 5
3 Project Title 5
Province2 4
District1 4
4 Project Title 2
5 Project Title 2

The no. of municipalities covered by the project are colored black. The district total of municipalities covered by the projects are colored in blue. The provincial total of municipalities covered by the projects are colored in green and the regional total of municpalities covered by the projects are colored in red.

I tried using count function but it returned a wrong answer

I hope someone can help me. Whoever you are, many Thanks in advance.

 

[arnold71]

To add to this dilemma there are several tables attached to the project table. Not only the municipality table but also the organizations and the communities table covered by the project. Therefore, there are another two tables which also needed to be counted per group/level. The report is called the Summary of the area/organization covered by the project per Region/Province/District

 

[vbaInet]

One step after the other, and to be honest I don't follow your second post.

As per your first post, join both tables via the appropriate ID using a query. Use this query as the record source of your report. Then use the Sorting & Grouping feature in the report to add your levels of grouping. Isn't that what you're asking for? Or you're not sure how to group in reports?