Grouping fields but having a date range in a query

[tanhus]

I have a query that lists the project, task code, hours by a team member, and
date. The date has a criteria which takes in a range from a form. The problem
is that i get a list of each invidual entry by the date, like so.

Project Task Code Hours By a Team Member Date
Project1 1 10
06/08/06
Project1 1 5
06/09/06
Project1 2 10
06/10/06
Project1 2 10
06/11/06
Project1 2 10
06/08/06

I want it so that that the date doesnt show up and to group the task codes
and projects together so that it looks like this.

Project Task Code Hours By a Team Member
Project1 1 15
Project1 2 30

Thanks to those who can help

 

[Ken Sheridan]

Group the query by project and task code and SUM the hours column. The
parameters need only be referenced in the WHERE clause so the dates won't be
returned in the result set, e.g.

SELECT Project, [Task Code], SUM([Hours Worked]) As [Total Hours]
FROM YourTable
WHERE [Date] >= Forms!YourForm!txtStartDate
AND [Date] < Forms!YourForm!txtEndDate + 1
GROUP BY Project, [Task Code];

BTW I'd avoid using Date as a column name; it could be confused with the
built in Date function in some circumstances and give unexpected results.
Its better to use terms like TransactionDate or whatever suits.

Ken Sheridan
Stafford, England

 

[John Spencer]

Try changing the GROUP BY under your date field to WHERE. That way in won't
show up in your columns and it won't be used to aggregate the data by date.
Where gets applied before the records are aggregated.

 

[tanhus]

Thanks

Group the query by project and task code and SUM the hours column. The
parameters need only be referenced in the WHERE clause so the dates won't be
returned in the result set, e.g.

SELECT Project, [Task Code], SUM([Hours Worked]) As [Total Hours]
FROM YourTable
WHERE [Date] >= Forms!YourForm!txtStartDate
AND [Date] < Forms!YourForm!txtEndDate + 1
GROUP BY Project, [Task Code];

BTW I'd avoid using Date as a column name; it could be confused with the
built in Date function in some circumstances and give unexpected results.
Its better to use terms like TransactionDate or whatever suits.

Ken Sheridan
Stafford, England

I have a query that lists the project, task code, hours by a team member, and
date. The date has a criteria which takes in a range from a form. The problem
is that i get a list of each invidual entry by the date, like so.

Project Task Code Hours By a Team Member Date
Project1 1 10
06/08/06
Project1 1 5
06/09/06
Project1 2 10
06/10/06
Project1 2 10
06/11/06
Project1 2 10
06/08/06

I want it so that that the date doesnt show up and to group the task codes
and projects together so that it looks like this.

Project Task Code Hours By a Team Member
Project1 1 15
Project1 2 30

Thanks to those who can help