Help with the output of a query

[pupli]

Hi everybody,

I need some with the results of a query.

I have this table

EMPLOYES

-------------------------
Name |Salary |Company |
-------------------------
Andrew | 1245 | ABC |
-------------------------
Mark | 1521 | ASD |
-------------------------
Mary | 5484 | EDC |
-------------------------
Andrew | 5124 | DEA |
-------------------------
John | 3521 | ASD |
-------------------------
John | 6124 | DEA |
-------------------------

I want my query to output the following result:

Name Salary Company
----- ------- --------
Andrew 6369 ABC + DEA
Mark 1521 ASD
Mary 5484 EDC
John 9645 ASD + DEA

How can I do this, with the "Company" field?
This field is of type string

The only thing I can say, is that I can realise this output on a SQL Server
by running this query:

SELECT t.Name,t.Salary,LEFT(cl.CompList,LEN(cl.CompList)-1) AS Company
FROM (SELECT Name,SUM(Salary) AS Salary
FROM EMPLOYEES
GROUP BY Name) t
CROSS APPLY (SELECT Company+ '+'
FROM EMPLOYEES
WHERE Name=t.Name
FOR XML PATH(''))cl(CompList)

===
How's it possible in access 2003 (or even 2002) ?

 

[Duane Hookom]

There is a generic concatenate function with sample usage at
http://www.rogersaccesslibrary.com/forum/forum_topics.asp?FID=4&SID=6cz48a9a8fz6af9c8cc1z7a7z562215z.

 

[pupli via AccessMonster.com]

I wonder if it will work on Access 2003 (or 2002).

I'll try and then I'll let you know

 

[pupli via AccessMonster.com]

hi again,

I'm sorry for disturbing again, but that concate function didn't succeed at
all

can u provide more help pls ?

 

[Duane Hookom]

Did you include the module in your mdb? "didn't succeed at all" doesn't tell
us anything about your results.

 

[pupli via AccessMonster.com]

Well,
By re-adapting the query for just "ONE" table, the results were different
from the one I firstly supoused to achieve. Working on a single table (like
the one I mentioned in the previous example), isn't the proper path,...
aplying this query

SELECT employes.name,
Concatenate(
"SELECT company & Chr(43) & company
FROM employes
WHERE ID_EMP =" & [ID_EMP]
) AS Lloji_TAMAM
FROM employes;

On this table:
ID_EMP | Name | Salary |Company |
-----------------------------------
1 | Andrew | 1245 | ABC |
----------------------------------
2 | Mark | 1521 | ASD |
---------------------------------
3 | Mary | 5484 | EDC |
----------------------------------
4 | Andrew | 5124 | DEA |
------------------------------------
5 |John | 3521 | ASD |
-----------------------------------
6 |John | 6124 | DEA |

we'll get an output like this:


ID_EMP | Name | Company |
-----------------------------------
1 | Andrew | ABC + ABC |
----------------------------------
2 | Mark | ASD + ASD |
---------------------------------
3 | Mary | EDC + EDC |
----------------------------------
4 | Andrew | DEA + DEA|
------------------------------------
5 |John | ASD + ASD |
-----------------------------------
6 |John | DEA + DEA |

Maybe I should try to work with two tables...
Meaning that, one of them will contain anly 2 fields,
ID_EMP and Name

 

[Duane Hookom]

Sum the Salary and group by ID_EMP, Name, and the Concatenate() function.