Grouping reports

[jfrazer22]

I have a project database that consist of general project information and a details header that list each division envolved in the project with the assigned Designer per division.

I need to add a grouping that will print a report that groups by Designer. But i still want to display all divisions per project. It currently filters out all other divisions and only shows the division per that manager. How can i do this?

Here is my current SQL Code with the grouping

SELECT Projects.[Project Name], [Design Fees].[Project ID], Projects.ID, Projects.[Project Number], Projects.Architect, Projects.[Project Manager], [Design Fees].Designer, Projects.Status, Projects.[Start Date], Projects.[End Date], Projects.[Next Due Date], [Design Fees].Division, [Design Fees].[Design Fee], [Design Fees].[Fee Billed], [Design Fees].[Actual Percent Complete], [Design Fee]*[Actual Percent Complete] AS [Value Completed], Divisions.Division, Projects.[Next Due Date Desc], DateDiffW(CDate(Now()),[End Date]) AS DaystoEnd, Projects.[Principle In Charge], [Design Fees].Staff
FROM ((Projects LEFT JOIN [Design Fees] ON Projects.ID = [Design Fees].[Project ID]) LEFT JOIN Employees ON Projects.[Project Manager] = Employees.ID) LEFT JOIN Divisions ON Employees.[Home Division] = Divisions.ID;

Here is basically what the form look like with out sorting by Designer

Project # -----Home Div-------- Project Name------- Client -------Principle-------- Manager
10110-4521 ----20 --------------test project---------- client -------Greg S------------ Dennis D


----------Division -------Design Fee------- Fee Used --------Percent -------Fee Complete ----------Value Complete ---------Designer
----------10-------------- $xx,xxx ---------------x% ---------------0%---------------- 0% ------------------------------------------------------Wes B
----------20 --------------$xx,xxx--------------- x% ---------------0% ----------------0% ------------------------------------------------------Dennis D
----------40-------------- $xx,xxx--------------- x% ---------------0%---------------- 0% ------------------------------------------------------Greg S
---------------------Total $xx,xxx

10111-4462 ----20 --------------test project2---------- client -------Tim L------------ Tracy D
----------Division -------Design Fee------- Fee Used --------Percent -------Fee Complete ----------Value Complete ---------Designer
----------10-------------- $xx,xxx ---------------x% ---------------0%---------------- 0% ------------------------------------------------------Gary W
----------20 --------------$xx,xxx--------------- x% ---------------0% ----------------0% ------------------------------------------------------Dennis D
----------40-------------- $xx,xxx--------------- x% ---------------0%---------------- 0% ------------------------------------------------------Greg S
---------------------Total $xx,xxx

Here is what it looks like when I group project by designer


Project # -----Home Div-------- Project Name------- Client -------Principle-------- Manager
Designer: Dennis D
10110-4521 ----20 --------------test project---------- client -------Greg S------------ Dennis D
----------Division -------Design Fee------- Fee Used --------Percent -------Fee Complete ----------Value Complete ---------Designer
----------20 --------------$xx,xxx--------------- x% ---------------0% ----------------0% ------------------------------------------------------Dennis D
---------------------Total $xx,xxx

10111-4462 ----20 --------------test project2---------- client -------Tim L------------ Tracy D
---------Division -------Design Fee------- Fee Used --------Percent -------Fee Complete ----------Value Complete ---------Designer
----------20 --------------$xx,xxx--------------- x% ---------------0% ----------------0% ------------------------------------------------------Dennis D
---------------------Total $xx,xxx


I still want to show the division 10 and 40 information.

Thanks for your help

[NTC]

Your first example is grouped by Project

Your second example is grouped by Project And grouped by Designer (not just by Designer).

I think what you are asking for is that it be grouped by Designer. So you need to delete the grouping by Project.

This should all be able to be done in the Report's standard grouping/sorting feature and not with any custom sql statement.