Grouping fields but having a date range in a query

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

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
 
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
 
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.
 
Thanks

Ken Sheridan said:
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

tanhus said:
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
Click to expand...
Click to expand...
 
Back
Top