I think a crosstab

[Marcie]

I have a table that has the following information
ID Code Hrs Amt Goal
25 WID 10
25 WCD 22
27 WMD 110
27 WCD 11
28 WID 15
29 WMD 100

etc....

I want to create a query that has the following results (There could be up
to 6 codes per id )
Id Code HRS AMT GOAL CODE HRS AMT GOAL
etc
25 WID 10 WCD 22
27 WMD 110 WCD 11
28 WID 15
29 WMD 100
Or it could be
ID WID WMD WCD etc...
25 10 22
27 110 11
28 15
29 100
Because WID are always amount WMD always amount and WCD are always hours
so I could "build" calculations based on column.


Thanks Marcie

 

[Jeanette Cunningham]

Hi Marcie,
this query:
SELECT tblGoals.ID, Sum(tblGoals.Hrs) AS WID, Sum(tblGoals.Goal) AS WMD,
Sum(tblGoals.Amt) AS WCD
FROM tblGoals
GROUP BY tblGoals.ID;

will give you this result:
ID WID WMD WCD
25 10 22
27 110 11
28 15
29 111

Jeanette Cunningham