obviously I can't test this without the ddl, but here is one way. Even if
this isn't quite right for you, you can get the idea. I have changed one of
the joins to a nested query, then joined against that.


---------------------------------
SELECT PROJ.ws_id,
X_RS.RS_TT__rs_ca_sh_name,
X_RS.RS_AR__rs_ca_sh_name,
X_RS.RS_KT__rs_ca_sh_name,
T_SPR.start_date, Sum(T_SPR.act_qty) AS Sum_qty,
Sum(T_SPR.remain_qty) AS Sumremain_qty,
Sum(T_SPR.act_cost) AS Sumact_cost,
Sum(T_SPR.remain_cost) AS Sumremain_cost
FROM ((PROJ INNER JOIN (select TS.taskrsrc_id, TS.rsrc_Id, TASK.ws_ID
from TS INNER JOIN TASK ON TS.task_id
= TASK.task_id)
as TS ON PROJ.ws_id = TS.ws_id)
INNER JOIN T_SPR ON TS.taskrsrc_id = T_SPR.taskrsrc_id)
INNER JOIN X_RS ON TS.rsrc_id = X_RS.rsrc_id
GROUP BY PROJ.ws_id,
X_RS.RS_TT__rs_ca_sh_name,
X_RS.RS_AR__rs_ca_sh_name,
X_RS.RS_KT__rs_ca_sh_name,
T_SPR.start_date

---------------------------

And, while it may not be optimized, this should also work.

------------------------------
SELECT PROJ.ws_id,
X_RS.RS_TT__rs_ca_sh_name,
X_RS.RS_AR__rs_ca_sh_name,
X_RS.RS_KT__rs_ca_sh_name,
T_SPR.start_date, Sum(T_SPR.act_qty) AS Sum_qty,
Sum(T_SPR.remain_qty) AS Sumremain_qty,
Sum(T_SPR.act_cost) AS Sumact_cost,
Sum(T_SPR.remain_cost) AS Sumremain_cost
FROM PROJ
INNER JOIN TASK on PROJ.ws_id = TASK.ws_id
INNER JOIN TS ON TS.task_id = TASK.task_id
INNER JOIN T_SPR ON TS.taskrsrc_id = T_SPR.taskrsrc_id
INNER JOIN X_RS ON TS.rsrc_id = X_RS.rsrc_id
GROUP BY PROJ.ws_id,
X_RS.RS_TT__rs_ca_sh_name,
X_RS.RS_AR__rs_ca_sh_name,
X_RS.RS_KT__rs_ca_sh_name,
T_SPR.start_date

-------------------------------------

[quoted text, click to view]

I've got this query that works just fine in access.

In it's query it makes use of some linked tables that it groups and sums,
taking the query into sql-server doesn't work and I need some help
understanding it

As I understood from reading access uses (can use) functions such as sum and
joins to return whole tables (resultsets from tables that it then goups ?),
I don't get it and I don't understand how to translate a query like the one
below to get it to work on sql-server.

If you can help me out with this I'd appreciate it

If you can't examplify the weid join for me using these tables, please use
others and help me out...
How can you do like this in sql-server ((PROJ INNER JOIN (TS INNER JOIN TASK
ON TS.task_id = TASK.task_id) ON PROJ.ws_id = TASK.ws_id) ?


SELECT PROJ.ws_id,
X_RS.RS_TT__rs_ca_sh_name,
X_RS.RS_AR__rs_ca_sh_name,
X_RS.RS_KT__rs_ca_sh_name,
T_SPR.start_date, Sum(T_SPR.act_qty) AS Sum_qty,
Sum(T_SPR.remain_qty) AS Sumremain_qty,
Sum(T_SPR.act_cost) AS Sumact_cost,
Sum(T_SPR.remain_cost) AS Sumremain_cost

FROM

((PROJ INNER JOIN (TS INNER JOIN TASK ON TS.task_id = TASK.task_id) ON
PROJ.ws_id = TASK.ws_id) INNER JOIN T_SPR ON TS.taskrsrc_id =
T_SPR.taskrsrc_id) INNER JOIN X_RS ON TS.rsrc_id = X_RS.rsrc_id GROUP BY
PROJ.ws_id, X_RS.RS_TT__rs_ca_sh_name, X_RS.RS_AR__rs_ca_sh_name,
X_RS.RS_KT__rs_ca_sh_name, T_SPR.start_date;