Group Query Results

[andy_dyer]

Hi,

I have a query:

SELECT qryProjectSelect.[Department Name] AS Department, Sum(qryProjectSelect.[Total Hrs]) AS [Total Hours], [Total Hours]/[Grand Total] AS Percentage
FROM qryProjectSelect, qryProjectTotalHours
WHERE (((qryProjectSelect.[Entry Date]) Between [forms]![frmQuery]![txtStart] And [forms]![frmQuery]![txtFinish]))
GROUP BY qryProjectSelect.[Department Name], qryProjectTotalHours.[Grand Total]
ORDER BY Sum(qryProjectSelect.[Total Hrs]) DESC;

This gives me the results I need broken down by individual department...

I need to be able to group department a with department e and departments b, c and d...

I have tried adding an extra layer of hierarchy in my tblDept with a field "New Department" but am not sure how I can feed this into the query at all so that it is the new department for this query that is picked up and not the original department name...

Have I explained that ok??

If anyone can help I'd be grateful!

 

[ezfriend]

You can also create a new query using the same query you have in your question, but filter out the department in your WHERE clause and group by your "A & E" department name

For the Department name, maybe use something like

SELECT "A & E" as Department, ........
WHERE [Department Name] IN("a","E")
GROUP BY "A & E", .....

Once these queries are created, you can use a UNION to display them together.

 

[DCrake]

Andy

Are you saying that there are now two levels of departments?

Example

+   Level 1
     - Level 1.1
     - Level 1.2
     - Level 1.3
+ Level 2
     - Level 2.1
     - Level 2.2
     - Level 2.3
+ Etc
     - Etc

If so you need to introduce this into your queires at grouping level and remove the sub departments.