summarize table

[sugargenius]

I need to summarize a table that looks like this:

WBS DIV RES MONTH HOURS DIRECT
1.01.01.01 22 EE1 FEB-05 5 100
1.01.01.01 22 EE1 FEB-05 10 200
1.01.01.01 22 EE1 FEB-05 15 300
1.01.01.01 22 EE1 JUN-05 20 400
1.01.02.01 22 DD1 APR-05 25 500
1.01.02.01 22 PHYS1 FEB-05 30 600
1.01.02.01 22 PHYS1 MAR-05 35 700

into this:

WBS DIV RES MONTH HOURS DIRECT
1.01.01.01 22 EE1 FEB-05 30 600
1.01.01.01 22 EE1 JUN-05 20 400
1.01.02.01 22 DD1 APR-05 25 500
1.01.02.01 22 PHYS1 FEB-05 30 600
1.01.02.01 22 PHYS1 MAR-05 35 700

in sql, I would do something like:
SELECT WBS, DIV, RES, MONTH, SUM(HOURS) AS HRS, SUM(DIRECT) AS DIR FROM
TABLE1 GROUP BY WBS, DIV, RESCODE, MONTH

It seems like this could be done with pivot tables, but I'm not
familiar enough with them to know where to put each field.

Woody

 

[Tom Ogilvy]

WBS, DIV, RES, MONTH would be row fields

HOURS and DIRECT would be DATA Fields.

Once you create the table, you would probably have

Hours
Direct

Just select the header (Data button) and drag it one cell to the Right,
then release. This will put the data in columns instead of rows.