Summary Query-How To

[Rattlesnake]

Hi,

I have a table "SWReports" that stores the Service Reports of our engineers.

The table structure is as follows:

 

SWReports

-----------

1. ReportNo

2. EmpNo

3. WeekNo

4. HrsWorked

5. ReportStatus (Approved or WaitingApproval)

 

An engineer can have MULTIPLE Reports for the same week.

 

I want to create a query that will give me the Hrs Approved and Hrs WaitingApproval for each employee PER WEEK

Something like this

 

1. EmpNo

2. WeekNo

3. ApprovedHrs (Sum of HrsWorked in reports that are Approved)

4. WaitingHrs (Sum of HrsWorked in reports that are WaitingApproval)

 

 

How can I build this query?

 

 

Fo rinfo, the table is stored in a MS SQL Server Database

[Gill Bates]

This should group the data for you:

  SELECT EmpNo, WeekNo, SUM(HrsWorked), ReportStatus
   FROM SWReports
GROUP BY EmpNo, WeekNo, ReportStatus