SUM/APPEND text in a select query

[meacho]

I have a select Query in which i sum one field

ID |Location | value | Cat
1 | A | 10 | 23
1 | B | 11 | 24
1 | B | 10 | 34
2 |A | 1 | 37



Using
Select [ sum ]
From [ ]
Group BY []
so far i have the following EXCEPT i can't figuer out how to display the Cat field


ID | Location | Value | Cat
1 | A | 10 | 23
1 | B | 21 | 24, 34
2 | A | 1 | 37


Any Ideas?

 

[ted.martin]

Try creating a second query where the Unique ID (GroupBy?) in your summing Query is joined to the Unique ID in the Main Table

 

[meacho]

sorry i dont exacty follow that

 

[meacho]

this is part of the code i'm using

SELECT DISTINCT PL_Volume_AC_Calculated.Project_ID , PL_Volume_AC_Calculated.UID_Desc,  PL_Volume_AC_Calculated.Principal_Contractor, PL_Volume_AC_Calculated.UID,  PLBU_ACT.Completion_Units_FY, PLBU_ACT.CurrentBudget_FY
FROM  PL_Volume_AC_Calculated LEFT JOIN

(SELECT DISTINCT PL_Volume_AC_Calculated.UID, sum(PL_Volume_AC_Calculated.Completion_Units) AS Completion_Units_FY,PL_Volume_AC_Calculated.Project_ID , PLBU.CurrentBudget_FY
From PL_Volume_AC_Calculated LEFT JOIN

(SELECT DISTINCT PL_Volume_BU_Calculated.UID, sum(PL_Volume_BU_Calculated.Budgeted_Units) AS CurrentBudget_FY, PL_Volume_BU_Calculated.Project_ID
FROM PL_Volume_BU_Calculated RIGHT JOIN TEMP_Project_Selection
ON PL_Volume_BU_Calculated.PROJECT_ID = TEMP_Project_Selection.Project_ID
Where (PL_Volume_BU_Calculated.Delivery_Year = '07/08')
GROUP BY PL_Volume_BU_Calculated.UID, PL_Volume_BU_Calculated.Project_ID) AS PLBU

ON (PL_Volume_AC_Calculated.Project_ID = PLBU.Project_ID) AND ( PL_Volume_AC_Calculated.UID = PLBU.UID)
Where (PLBU.CurrentBudget_FY <> 0)
Group by  PL_Volume_AC_Calculated.UID, PL_Volume_AC_Calculated.Project_ID , PLBU.CurrentBudget_FY) AS PLBU_ACT

ON (PL_Volume_AC_Calculated.Project_ID = PLBU_ACT.Project_ID) AND ( PL_Volume_AC_Calculated.UID = PLBU_ACT.UID)
Where (PLBU_ACT.CurrentBudget_FY <> 0)

 

[ted.martin]

Are you using queries or writing your own SQL? If the latter then I will have to bale out. What I was suggesting is that you use two queries, one to do the summing and then another to draw the answer into a new query which pulls through the CAT value from an Join. (You can always convert the query back to SQL afterwards if needed).

With complex queries that get a bit complex and summing does tend to do this, in my experience it is better to build the answer one step at a time. Sorry if this doesn't take you forward.