Combine fields

[Foocat]

All please help,

here is my table.

Year GNAME Job # BCODE Job Name SumOfHOURS
2007 KWA 101-07 Services BD's Deerfield 27
2007 KWA 101-07 EC BD's Deerfield 7
2007 KWA 102-07 Services Batting Cages 4
2007 KWA 102-07 EC Batting Cages 5
2007 KWA 103-07 Services 3914 Lindell 44
2007 KWA 103-07 EC 3914 Lindell 14

I need a query to make it look like this.


Year GNAME Job # Job Name xBCODE yBCODE xSumOfHOURS ySumOfHOURS
2007 KWA 101-07 BD's Deerfield Services EC 27 7
2007 KWA 102-07 Batting Cages Services EC 4 5
2007 KWA 103-07 3914 Lindell Services EC 44 14

 

[Foocat]

Hey, I answered my own question.

UNION QUERY


SELECT [Job List].Year AS [YEAR], [Job List].[Job Name] AS JOBNAME,
ACTIVITY.DATE AS [DATE], ACTIVITY.JOBNUMBER AS JOBNUMBER,
Choose(Left([jobnumber],1),"KWA","Panara
Bread","MGDF","","WJA","","CITTA/HOTZ","MISC.","MISC.") AS GNAME, "EC"
AS TYPE, ACTIVITY.HOURS AS ECHOURS,0 AS CDHOURS

FROM ([Job List] INNER JOIN ACTIVITY ON [Job List].[Job #] =
ACTIVITY.JOBNUMBER) INNER JOIN ACTCODE ON ACTIVITY.ACTCODE =
ACTCODE.ACODE

WHERE (((ACTIVITY.ACTCODE)="SR" Or (ACTIVITY.ACTCODE)="SV"))

UNION ALL SELECT [Job List].Year AS [YEAR], [Job List].[Job Name] AS
JOBNAME, ACTIVITY.DATE AS [DATE], ACTIVITY.JOBNUMBER AS JOBNUMBER,
Choose(Left([jobnumber],1),"KWA","Panara
Bread","MGDF","","WJA","","CITTA/HOTZ","MISC.","MISC.") AS GNAME,
"Services" AS TYPE,0 AS ECHOURS,ACTIVITY.HOURS AS CDHOURS

FROM ([Job List] INNER JOIN ACTIVITY ON [Job List].[Job #] =
ACTIVITY.JOBNUMBER) INNER JOIN ACTCODE ON ACTIVITY.ACTCODE =
ACTCODE.ACODE

WHERE (((ACTIVITY.ACTCODE)="BD" Or (ACTIVITY.ACTCODE)="CA" Or
(ACTIVITY.ACTCODE)="CD" Or (ACTIVITY.ACTCODE)="PD" Or
(ACTIVITY.ACTCODE)="RV" Or (ACTIVITY.ACTCODE)="AD"))
ORDER BY YEAR DESC , JOBNUMBER;