Showing a sum based on a field name...

[DMcCallister]

So I have a Project Database. For the sake of simplicity lets say I have two tables.
1. An Employee Task Table, which houses the tasks the employees have done and to what project


2. and a Project Table which houses all the pertinent Project information such as Name of Project, Due Date, ID Number of Project, Status and so forth.

Employees do Tasks that are associated with a Project. So for example, Employee A Codes Project 1 in 10 hours, Employee B Debugs Project 1 in 2 hours. Employee A Codes Project 2 in 20 hours and Employee B Debugs Project 2 in 4 hours.

I am running a Report on the Projects and need to display the total time for Project 1 and total time for Project 2. Currently what I am getting is the Total Hours for Project 1 and 2 combined (so 36 hours in this example). I need to end up with 12 hours for Project 1 and 24 hours for Project 2. Is there an easy way to single them out?

**I'm not to familiar with Reports so if there is a feature I missed or a step in the Report Wizard I should use I apologize.

[burrina]

Use Group By Project for your criteria!

[orange]

Maybe I've missed something in your set up, but it sounds like Task is not defined/evident.
Seems to me you couldn't determine Hrs worked per Task.

I see a Project made up of 1 or more Tasks
A Person works on a Task that is related to a Process
May be possible for 1 or more People to work on same Task
Is a Task uniquely identified with a Project, or can many Projects have the same Task name?

Person--->PersonWorksOnTask<----Task<--------Project

[DMcCallister]

Maybe I've missed something in your set up, but it sounds like Task is not defined/evident.
Seems to me you couldn't determine Hrs worked per Task.

I see a Project made up of 1 or more Tasks
A Person works on a Task that is related to a Process
May be possible for 1 or more People to work on same Task
Is a Task uniquely identified with a Project, or can many Projects have the same Task name?

Person--->PersonWorksOnTask<----Task<--------Project

Projects can have the same Task Name.

UserID	Project	Task	Hours
A1234	Project 1	Code Development	15
B1234	Project 1	Code Development	12
C1234	Project 1	Debugging	3
A1234	Project 1	Debugging	10
B1234	Project 2	Code Development	50
C1234	Project 2	Debugging	10

In the report for Projects I need to display all Projects and the Total Hours. So Project 1 should be 40 Hours, but I'm displaying 100.

[orange]

You have a database structure problem for normalized tables/database.

A query such as this should work.

Select Project, Sum(Hours)
FROM yourTable
WHERE  Project = "Project 1";

[June7]

Looks like projects have multiple tasks. I see 40 hours for Project 1, regardless of task.

SELECT Project, Sum(Hours) AS ProjectHrs GROUP BY Project;

If you want to show detail records, build a report and use Sorting & Grouping features with aggregate calcs in footers and don't do aggregate calcs in query.