Mltiple Group By concept?

[cory.plowman]

Okay guys, i'm fairly new at all this, so here's what I'm trying to do.

I have a query that has the following fields:

Date
Task
Task_Number (number of items completed for task)
Employee


I want to take that query and generate two reports that will tell me the following:

1) Total number per task per date
2) Individual number per task per employee per date

The problem I'm running into is that each task can appear multiple times per day, as we have multiple employees assigned to each task. I don't want it to list each instance of task (which is what the report is doing now), but rather a total sum of Task_Number for each task each day.

This is my current query for Total Task_Number per date:



SELECT DISTINCT Data.date_id, Data.Task, Data.Task_num
FROM Data
GROUP BY Data.date_id, Data.Task, Data.Task_num
ORDER BY Data.date_id;

update: here is a screenshot of the report I'm working with, hopefully to illustrate my point.

[NTC]

ok - well the first point to make is that one can aggregate at 2 different levels; you can do an aggregate query - and make a report based on that - or - you can aggregate with the report itself.

Not a right or wrong - just a matter of what you want.

When you aggregate in a report - that is in the group footer. Most people want to see the detail section. If you do not - then make that section not visible.

Hope it helps.

[cory.plowman]

I thought that's what I had been doing, but am I doing it wrong? I want it grouped by date, then by task, then to SUM the task_number line according to the Task grouping.

[NTC]

and so looking at your added data:
8/8/11 Stretch = 16 right?

[boblarson]

Do the grouping in the Report's SORTING AND GROUPING, not in the query.